json where clause in postgresql

Question

I have save data in PostgreSQL as given below:

{"tags": "Tag 1,Tag 2,Tag 3"}
{"tags": "Tag 1,Tag 4,Tag 5"}
{"tags": "Tag 6,Tag 1,Tag 2"}

I want search records where 'Tag 2' or Tag 3 exists?

Table schema, create procedure is as below,

--create table 
CREATE TABLE "tblIrsInputTagging" ( 
  "IrsInputTaggId" serial NOT NULL, 
  "Irs_tags" json NOT NULL, CONSTRAINT "tblIrsInputTagging_pkey" 
  PRIMARY KEY ("IrsInputTaggId") 
) WITH ( OIDS=FALSE ); 
ALTER TABLE "tblIrsInputTagging" OWNER TO "postgre";

--insert json record 

INSERT INTO "tblIrsInputTagging" ("Irs_tags") 
VALUES ( '{"tags": "Tag 1,Tag 2,Tag 3"}' );

INSERT INTO "tblIrsInputTagging" ("Irs_tags") 
VALUES ( '{"tags": "Tag 1,Tag 4,Tag 5"}' ); 

INSERT INTO "tblIrsInputTagging" ("Irs_tags") 
VALUES ( '{"tags": "Tag 6,Tag 1,Tag 2"}' );

are those taglists, i mean, "Tag1, Tag2, Tag3" an array, or just a text? — marmeladze
– marmeladze, Commented May 28, 2017 at 4:26

chappy wolf · Accepted Answer · 2020-04-13 03:51:13Z

1

i don't think there is some specific function to check per json items. But you can do so by casting the data type as text then check it with like or ilike operator.

SELECT col1, col2 FROM table_name WHERE CAST(col1 AS text) ilike '%value%'

notice that the col1 column is the json data type.

answered Apr 13, 2020 at 3:51

chappy wolf

135 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2017-05-29 06:39:43Z

0

Let's say the column name is tags. In that case, you can query like below.
If there is something specific, let me know, so I might need to update below snippet.

SELECT col_1, col_2, .. col_n 
FROM your_table 
WHERE tags::json->'tags' ILIKE '%Tag2%'
OR
tags::json->'tags' ILIKE '%Tag3%'

edited May 29, 2017 at 6:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered May 28, 2017 at 4:33

marmeladze

6,5863 gold badges28 silver badges49 bronze badges

7 Comments

Laurenz Albe Over a year ago

Looks like your LIKE pattern is missing wildcards.

Narender Pal Over a year ago

ERROR: column "Tag2" does not exist LINE 2: WHERE "Irs_tags"::json->'tags' ILIKE "Tag2" ^ ********** Error ********** ERROR: column "Tag2" does not exist SQL state: 42703 Character: 73

Narender Pal Over a year ago

Dear Laurenz Albe its showing error that column "Tag 2" doses not exist even its not a column it is data inside the tag column of jason.

marmeladze Over a year ago

@NarenderPal please update your question body with that error message.

Narender Pal Over a year ago

ERROR: column "%Tag2%" does not exist still same error

|

Collectives™ on Stack Overflow

json where clause in postgresql

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related