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


[{ "_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.


2 Answers 2


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.


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.


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.


3. Extract value for nested key 'count'

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


In postgres 12 or later, you can use jsonb_path_query.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.