Check if a String exists in Postgres JSONB array

Question

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"]```

user330315 · Accepted Answer · 2021-06-22 14:40:55Z

8

that's what the ? operator will do:

select *
from the_table
where numbers ? '16516516'

Online example

answered Jun 22, 2021 at 14:40

user330315

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

Comments

Jim Jones · Accepted Answer · 2021-06-22 15:12:53Z

5

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)

edited Jun 22, 2021 at 15:12

answered Jun 22, 2021 at 14:44

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

2 Comments

Md. Sajedul Karim Over a year ago

Thanks, @jim for this nice answer. I am searching for two more questions: 1. How to implement contains some for an array. Here, some of elements should contains in jsonb column ["54954565","16516516"] 2. How to implement contains none for an array. Here, none of elements should contains in jsonb column ["54954565","16516516"] Thanks in advance.

Jim Jones Over a year ago

@Md.SajedulKarim thanks. I'm afraid I did not understand your question. Could you post a question with some sample data and the expected result? cheers

Collectives™ on Stack Overflow

Check if a String exists in Postgres JSONB array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related