How to use LIKE on elements of a JSONB array?

Question

I need to search with LIKE or ILIKE - applied to elements of a JSONB[] column.

I can use unnest() to display nested JSONB elements separately, but I am having trouble combining that with my LIKE expression. My query:

SELECT
    UNNEST( column_jsonb[] )
FROM
    table1
WHERE
    UNNEST( column_jsonb[] ) ->> 'member_type' LIKE '%member%'

Results in an error:

argument of WHERE must not return a set

How to get this right?

`UNNEST` is for expanding arrays to rows. If you're using that approach then use `UNNEST` in a subquery. — Dai, Jan 25 '19 at 02:13
Really `jsonb[]`? Not a plain `jsonb` with a JSON array inside? (Typically makes more sense.) And `column_jsonb[]` is not a legal identifier. Please clarify your question and add your version of Postgres. `SELECT version()` helps. — Erwin Brandstetter, Jan 25 '19 at 02:17
Use `jsonb_array_elements` instead of `unnest` for JSON. See: https://stackoverflow.com/questions/36174881/how-to-turn-a-json-array-into-rows-in-postgres - but we need to know the *actual* schema of your table and JSON data. Can you please post some example data? — Dai, Jan 25 '19 at 02:17

Erwin Brandstetter · Accepted Answer · 2019-01-25T02:28:36.967

0

If it's indeed a jsonb[] column:

SELECT * -- or whatever
FROM   t, unnest(jsonb_col) j
WHERE  j->>'member_type' LIKE '%member%';

That's an implicit CROSS JOIN LATERAL.

If it's a JSON array in a plain jsonb column (more likely):

SELECT *
FROM   t1, jsonb_array_elements(jsonb_col) j
WHERE  j->>'member_type' LIKE '%member%';

edited Jan 25 '19 at 02:28

answered Jan 25 '19 at 02:23

1 Answers1