10

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.

15

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:

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:

3. Extract value for nested key 'count'

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

  • 1
    Where does the obj(value) come from? Is it on the LATERAL JOIN, the jsonb_array_elements or somewhere else? – Tyler DeWitt Jan 26 at 9:11
  • It looks like the formatting might have gotten screwed up. Am I reading it correctly that JOIN LATERAL jsonb_array_elements(t.usr) obj(value) is short for JOIN LATERAL jsonb_array_elements(t.usr) AS obj(value) and that obj(value) is a table and column alias? In this example, if obj is a table alias, what is it an alias to? The set returned from jsonb_array_elements? – Tyler DeWitt Jan 27 at 7:55
  • 1
    yes, and yes. i removed my scrambled comment. – Erwin Brandstetter Jan 27 at 18:11
  • Is there a need to use the column alias? In my testing, JOIN LATERAL jsonb_array_elements(t.usr) obj ON obj->>'_id' = '1' had the same effect (once you update the select statement to use value instead of val). It appears that jsonb_array_elements(t.usr) returns a table with only one column. Is postgres being smart and realizing that obj ->> is the same as obj.val ->>? – Tyler DeWitt Jan 28 at 2:14
  • With only a single column, Postgres uses a given alias as table and column name. I am just being explicit as there are many set-returning functions that return more than one column. – Erwin Brandstetter Jun 27 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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