Search code examples
postgresqlpostgresql-9.4jsonb

Why can't I query directly on jsonb_array_elements?

I have data stored as jsonb in a column called "data":

{'people': [{"name": "Bob", "Occupation": "janitor"}, {"name": "Susan", "Occupation", "CEO"}]}

I can query this via:

SELECT mydata.pk FROM mydata, jsonb_array_elements(mydata.data->'people') AS a WHERE (a->>'name') = 'bob'

Why can't I substitute "a" for the jsonb_array_elements(...)?:

SELECT mydata.pk FROM mydata WHERE (jsonb_array_elements(mydata.data->'people')->>'name') = 'bob'

Instead, I get the following:

ERROR:  argument of WHERE must not return a set
Solution

  • As the error message says, arguments to WHERE must not return a set. jsonb_array_elements returns a set and it can't be compared to a single value. In the second query you have a cross join inside the select and that converts it into a suitable result to use WHERE on.

    You can also do it this way

    SELECT mydata.pk FROM mydata
  WHERE 'Bob' in (SELECT jsonb_array_elements(mydata.data->'people')->>'name');

    Here the subselect will allow you to use the IN operator to find the desired value since the result is no longer a set.

    Another way is to query the jsonb directly

    SELECT mydata.pk FROM mydata
  WHERE mydata.data->'people' @> '[{"name":"Bob"}]'::jsonb;

    This way you don't need to convert the jsonb into a resultset and search within it.