Why can't I directly request jsonb_array_elements?

Question

I have data stored as jsonb in the "data" column:

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

I can request this via:

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

Why can't I replace "a" with 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, the arguments WHEREshould not return the set. jsonb_array_elementsreturns a set, and it cannot be compared with a single value. In the second query, you have a cross join inside select and it will convert it to a suitable result to use WHEREon.

You can also do it this way.

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

IN, , .

- jsonb

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

, jsonb .