Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
7 trim noise, format, clarify, links
source | link

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 wordreserved word and would require double quoting to be used as column name. Don't do that. I'm naming the columnI use usr instead.

Query

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

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

There are three basic steps3 basic steps:

1. Identify qualifying rows cheaply

Find qualifying rows (quickly!).

WHERE t.usr @> '[{"_id":"1"}]' finds objectsidentifies rows with matching object in the JSON array - with any number of additional keys.

This The expression can use a generic GIN index on the JSONjsonb 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

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements()jsonb_array_elements(). (unnest()unnest() is only good for Postgres arrays, not for arrays nested in a jsonb valuearray types.) Since we are only interested in actually matching objects, filter with ain the join condition right away.

Related:

3. Extract value for nested key 'count'

Extract the value for the nested key 'count'.

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

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'm naming the column usr instead.

Query

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

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

There are three basic steps:

1.

Find qualifying rows (quickly!).

usr @> '[{"_id":"1"}]' finds objects in the array - with any number of additional keys.

This can use a generic GIN index on the JSON 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.

Related:

2.

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres arrays, not for arrays nested in a jsonb value.) Since we are only interested in actually matching objects, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

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

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

6 clarify, update links
source | link

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 wordreserved word and would require double quoting to be used as column name. Don't do that. I'm naming the column usr instead.

Query

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

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

There are three basic steps:

1.

Find qualifying rows (quickly!).

Note how usr @> '[{"_id":"1"}]' can findfinds objects in the array - with any number of additional keys.

YouThis can support this withuse a generic GIN index on the JSON 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 in this caselogically redundant, but it is required to use the index. The expressinexpression 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 for performance with big tables.

Related:

2.

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements()jsonb_array_elements(). (unnest()unnest() is only good for Postgres arrays, not for arrays nested in a jsonb value.) Since we are only interested in actually matching objects, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

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

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'm naming the column usr instead.

Query

The query is not as trivial as the comments make it seem:

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

There are three basic steps:

1.

Find qualifying rows (quickly!).

Note how usr @> '[{"_id":"1"}]' can find objects in the array with any number of additional keys.

You can support this with a generic GIN index on the JSON column, or 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 in this case, but it is required to use the index. The expressin in the join clause enforces the same condition - but 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 for performance with big tables.

Related:

2.

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres arrays, not for arrays nested in a jsonb value.) Since we are only interested in actually matching objects, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

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

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'm naming the column usr instead.

Query

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

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

There are three basic steps:

1.

Find qualifying rows (quickly!).

usr @> '[{"_id":"1"}]' finds objects in the array - with any number of additional keys.

This can use a generic GIN index on the JSON 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.

Related:

2.

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres arrays, not for arrays nested in a jsonb value.) Since we are only interested in actually matching objects, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

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

5 remove unnecessary subquery, update links, explain, fix format, trim noise
source | link

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 wordreserved word and would require double quoting to be used as column name. Don't do that. I'm naming the column usr instead.

Query

The query is not as trivial as the comments make it seem:

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

There are threethree basic steps:

  1. Find qualifying rows (quickly!).

1.

Find qualifying rows (quickly!).

Note how usr @> '[{"_id":"1"}]' can find objects in the array with any number of additional keys.

You can support this with a generic GIN index on the JSON column, or with the more specialized operator class jsonb_path_ops:

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

The added WHERE clause is logically redundant in this case, but it is required to use the index. The expressin in the join clause enforces the same condition - but 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 for performance with big tables.

Related:

2.

I achieve this by unnestingIdentify the matching object(s) in the array.

Unnest with jsonb_array_elements()jsonb_array_elements(). (unnest()unnest() is only good for Postgres arrays, not for JSON arrays nested in a jsonb value.) And sinceSince we are only interested in actually matching objects I add, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

  1. Extract the value for the key 'count'.

After qualifying objects have been extracted, this is simply: obj.value->>'count'.

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'm naming the column usr instead.

Query

The query is not as trivial as the comments make it seem:

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

There are three basic steps:

  1. Find qualifying rows (quickly!).

Note how usr @> '[{"_id":"1"}]' can find objects in the array with any number of additional keys.

You can support this with a generic GIN index on the JSON column, or with the more specialized operator class jsonb_path_ops:

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

Related:

I achieve this by unnesting with jsonb_array_elements(). (unnest() is only good for Postgres arrays, not for JSON arrays.) And since we are only interested in actually matching objects I add a join condition right away.

Related:

  1. Extract the value for the key 'count'.

After qualifying objects have been extracted, this is simply: obj.value->>'count'.

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'm naming the column usr instead.

Query

The query is not as trivial as the comments make it seem:

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

There are three basic steps:

1.

Find qualifying rows (quickly!).

Note how usr @> '[{"_id":"1"}]' can find objects in the array with any number of additional keys.

You can support this with a generic GIN index on the JSON column, or 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 in this case, but it is required to use the index. The expressin in the join clause enforces the same condition - but 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 for performance with big tables.

Related:

2.

Identify the matching object(s) in the array.

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres arrays, not for arrays nested in a jsonb value.) Since we are only interested in actually matching objects, filter with a join condition right away.

Related:

3.

Extract the value for the nested key 'count'.

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

4 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link
3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
2 sharpen terms
source | link
1
source | link