Question 229558 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Selecting data using jsonb data in WHERE clause

I have the below table – assume tags column always have top level string keys:

name(TEXT) | tags(jsonb)
-----------+-------------------------------------------------------
teamA      | {"color": "green", "zone": "primary"}
teamB      | {"color": "green", "zone": "primary", "role": "user"}
teamC      | {"uid": "32-232-2dswdwsd", "availability": "condensed"}

Let’s say I want to select rows containing keys color & role from tags column

I could do:

SELECT * 
FROM table 
WHERE tags?'color' AND tags?'role';

or:

SELECT * 
FROM table 
WHERE tags->'color' IS NOT NULL 
  AND tags->'role' IS NOT NULL;

However the above query will become quite cumbersome as number of keys increase. Is there a better way to do the above query?

Tags:

2

Answers


  1. 0

    You can go with jsonb_exists function in postgreSQL.

    SELECT * 
FROM your_table
WHERE jsonb_exists(tags, '{color, role}');

    Formal documentation is not on official postgres site, you can find it by running doS+ in the psql console.

    Note: It might not be supported your postgres version.

    Login or Signup to reply.
  2. 0

    Like Mike already commented, the operator ?& makes that as short as possible:

    SELECT * 
FROM   tbl 
WHERE  tags ?& '{color,role}';

    The manual:

    Do all of the strings in the text array exist as top-level keys or array elements?

    To only accept keys with non-null values (like in your 2nd example), add jsonb_strip_nulls():

    SELECT * 
FROM   tbl 
WHERE  jsonb_strip_nulls(tags) ?& '{color,role}';

    The manual:

    Deletes all object fields that have null values from the given JSON value, recursively. Null values that are not object fields are untouched.

    The latter cannot use a plain index, though. You’d need a matching expression index.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search