sqldatabasepostgresqljsonbunnest

Find a json value in a jsonb array

I have a jsonb[] column in my table named jsonb_column, which contains an array of JSONB objects. For example:

jsonb_column = '{"{\"title\":\"title1\"}"
                ,"{\"title\":\"title2\"}"}'

I'm attempting to extract the JSONB object where the title field is equal to 'title2'.

SELECT *
FROM tablename,
jsonb_array_elements(jsonb_column) AS job
WHERE job->>'title' = 'title2';

It doesn't return anything. How to do it properly?

Solution

  • A mix-up of jsonb[] (containing jsonb values that, in turn, contain a plain JSON object) with a JSON array stored as jsonb.

    Type jsonb[]

    jsonb[] is a Postgres array. To unnest, use unnest():

    CREATE TABLE tbl (id int, js jsonb[]);

INSERT INTO tbl VALUES
  (1, '{"{\"title\":\"title1\"}","{\"title\":\"title2\"}"}')
;

SELECT *
FROM   tbl, unnest(js) AS job
WHERE  job->>'title' = 'title2';

    fiddle - dbfiddle currently swallows necessary backslashes in the display. I reported a bug.

    Related:

    JSON array in type jsonb

    This is where you could use jsonb_array_elements() like you tried:

    CREATE TABLE tbl (id int, js jsonb);

INSERT INTO tbl VALUES
  (1, '[{"title":"title1"},{"title":"title2"}]')
;

SELECT *
FROM   tbl, jsonb_array_elements(js) AS job
WHERE  job->>'title' = 'title2';

    fiddle

    See:

    But for big tables it's a lot more efficient to filter with the jsonb contains operator @>:

    SELECT *
FROM   tbl
WHERE  js @> '[{"title":"title2"}]';

    Or with a SQL/JSON path expression:

    SELECT *
FROM   tbl
WHERE  js @? '$[*] ? (@.title == "title2")';

    fiddle

    The last two can use a GIN index like:

    CREATE INDEX tbl_js_gin_idx ON tbl USING gin (js jsonb_path_ops);

    See: