PostgreSQL – Select Item from JSONB Array as Top-Level JSON Object

jsonpostgresql

I have JSONB column which stores the data in this format

[
    {
        "item1": "value1",
        "item2": "value2"
    },
    {
        "item1": "value3",
        "item2": "value4"
    }
]

I am trying to select 'item1' with query

SELECT jsonb_array_elements(data)->'item1' FROM test_table

Error I am getting is:

cannot extract elements from an object

How should I change the query to make it work?

Best Answer

You need to unnest the array elements to rows in the from clause:

select i.dt -> 'item1' as item1
from test_table t
  cross join jsonb_array_elements(t.data) as i(dt)

pg 9.3

UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items i2
       , json_array_elements(i2.images) elem
  WHERE  elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE  i2.id = i.id
AND    json_array_length(i2.images) < json_array_length(i.images);

SQL Fiddle.

Explain

Unnest the JSON array with json_array_elements() in a subquery using an implicit JOIN LATERAL for the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
JOIN to the base table and add another WHERE condition using json_array_length() to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.

pg 9.4

This gets much easier with jsonb and additional jsonb operators.


UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items cand
       , json_array_elements(cand.images) elem
  WHERE  cand.images @> '{[{"id":"note_1"}]}'::jsonb
  AND    elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE i2.id = i.id;

Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.

Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:

"CREATE INDEX ... USING VODKA"

Postgresql – How to get particular object from jsonb array in PostgreSQL

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup.

Assumptions

Assuming this table definition:

CREATE TABLE tbl (tbl_id int, usr jsonb);

"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.

Query

The query is not as trivial as the (now deleted) comments made it seem:

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';

There are 3 basic steps:

1. Identify qualifying rows cheaply

WHERE t.usr @> '[{"_id":"1"}]' identifies rows with matching object in the JSON array. The expression can use a generic GIN index on the jsonb column, or one with the more specialized operator class jsonb_path_ops:

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);

The added WHERE clause is logically redundant, but it is required to use the index. The expression in the join clause enforces the same condition but only after unnesting the array in every row qualifying so far. With index support, Postgres only processes rows that contain a qualifying object to begin with. Does not matter much with small tables, makes a huge difference with big tables and only few qualifying rows.

2. Identify matching object(s) in the array

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres array types.) Since we are only interested in actually matching objects, filter in the join condition right away.

How to turn json array into postgres array?

3. Extract value for nested key `'count'`

After qualifying objects have been extracted, simply: obj.val->>'count'.