How to get particular object from jsonb array in PostgreSQL?

Question

I have a field called 'user' that holds a json array that roughly looks like this:

"user":

[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]

Now I want a query like:

select count from tablename where id = "1"

I'm not able to get the particular field count from an array of json objects in PostgreSQL 9.4.

Answer 1

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.

Related:

• PostgreSQL operator uses index but underlying function does not
• Index for finding an element in a JSON array

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.

Related:

• 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'.

Answer 2

In postgres 12 or later, you can use jsonb_path_query.

WITH cte (
    js
) AS (
    VALUES ('[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]'))
SELECT
    jsonb_path_query(js::jsonb, '$[*] ? (@._id == "1")') ->> 'count'
FROM
    cte;