Why can't I query directly on jsonb_array_elements?

Question

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

Answer 1

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.