CODEWITHSUNDEEP
sqljsonpostgresql
Adarsh Bhadauria
Adarsh Bhadauria

Reputation: 85

Check if a String exists in Postgres JSONB array

I am looking for a way to find if a string exists in a Postgres column of type "jsonb" that has values like below:

id | numbers(jsonb)
1  | ["54954565","16516516","565196212"]
2  | ["195195159","252432","275782872"]
3  | ["54954565","61595161","728278"]
4  | ["245735435","75454","2782"]

Eg:

If "16516516" exists in array corresponding to any entry in numbers, I want to get the whole row.

So if I query for "16516516".

The row I get should be:

id | numbers
1  | ["54954565","16516516","565196212"]```

Upvotes: 3

Views: 13003

Answers (2)

Jim Jones
Jim Jones

Reputation: 19643

Use the contains operator @>

SELECT * FROM t 
WHERE numbers @> '["16516516"]'

Demo db<>fiddle:

WITH t (id ,numbers) AS (
  VALUES (1,'["54954565","16516516","565196212"]'::jsonb),
         (2,'["195195159","252432","275782872"]'::jsonb),
         (3,'["54954565","61595161","728278"]'::jsonb),
         (4,'["245735435","75454","2782"]'::jsonb)
)
SELECT * 
FROM t WHERE numbers @> '["16516516"]';
 id |                numbers                
----+---------------------------------------
  1 | ["54954565", "16516516", "565196212"]
(1 row)

Upvotes: 5

user330315
user330315

Reputation:

that's what the ? operator will do:

select *
from the_table
where numbers ? '16516516'

Online example

Upvotes: 8

Related Questions