CODEWITHSUNDEEP
sqljsonpostgresql
Sandeep
Sandeep

Reputation: 986

How to get data from postgresql json array field

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.

Upvotes: 0

Views: 731

Answers (1)

Kristo Mägi
Kristo Mägi

Reputation: 1684

By the documentation https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE for all your cases:

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 :)

Upvotes: 1

Related Questions