How to get data from postgresql json array field

Question

I have table field jsonb type and having below data.

{"roles": ["7", "73", "163"]}

I have to check "73" is present into or not by postgresql.

I have already search but the only gives solution for object of object not object of array.

I have tried below query but it does not work

SELECT * 
FROM table 
WHERE field->'roles' ? ARRAY ['73'];

--updated--

also I need only that record which have exact value

{"roles": ["7"]}

{"roles": ["7", "73", "163"]}

i.e. field only have "7" not anything else in it.

Why do you use ARRAY['73']? Your query works fine with just WHERE field -> 'roles' ? '73'. — Marth
– Marth, Commented Nov 8, 2016 at 10:25
@Marth: Thanks I have search over half day for solutions on internet and stack overflow but you give it in a minute. — Sandeep
– Sandeep, Commented Nov 8, 2016 at 10:31
@Marth: Also I need to match exact array of that. May be that's why I am using ARRAY['73'] but it does not works :( — Sandeep
– Sandeep, Commented Nov 8, 2016 at 10:58

Kristo Mägi · Accepted Answer · 2016-11-08 11:36:37Z

1

Does –single– key exists in json array:

SELECT  * 
FROM    table 
WHERE   field -> 'roles' ? '73';

Does -any- of the keys at right exists in json array:

SELECT  * 
FROM    table 
WHERE   field -> 'roles' ?| ARRAY[ '7', '163' ] ;

Does -all- of the keys at right exists in left json array:

SELECT  * 
FROM    table 
WHERE   field -> 'roles' ?& ARRAY[ '7', '163' ] ;

Does left json array match -exactly- with the right json array:

SELECT  * 
FROM    table 
WHERE   field -> 'roles' = $$[ "7" ]$$::jsonb ;

Hopefully helps :)

answered Nov 8, 2016 at 11:36

Kristo Mägi

1,70413 silver badges16 bronze badges

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

1 Comment

Thanks It helps me a lot. :)