SELECT on JSON operations of Postgres array column?

I have a column of type jsonb[] (a Postgres array of jsonb objects) and I’d like to perform a SELECT on rows where a criteria is met on at least one of the objects. Something like:

-- Schema would be something like
mytable (
  id UUID PRIMARY KEY,
  col2 jsonb[] NOT NULL
);

-- Query I'd like to run
SELECT
  id,
  x->>'field1' AS field1
FROM
  mytable
WHERE
  x->>'field2' = 'user' -- for any x in the array stored in col2

I’ve looked around at ANY and UNNEST but it’s not totally clear how to achieve this, since you can’t run unnest in a WHERE clause. I also don’t know how I’d specify that I want the field1 from the matching object.

Do I need a WITH table with the values expanded to join against? And how would I achieve that and keep the id from the other column?

Thanks!

Answer

You need to unnest the array and then you can access each json value

SELECT t.id,
       c.x ->> 'field1' AS field1
FROM mytable t
  cross join unnest(col2) as c(x)
WHERE c.x ->> 'field2' = 'user'

This will return one row for each json value in the array.

Advertisement

Answer