sql - Access (and count) just object values from Postgres JSONB array of objects

Question

I have a JSONB column in a Postgres database. I'm storing an array of JSON objects, each with a single key-value pair. I'm sure I could have designed this better, but for now I'm stuck with this.

id | reviews
------------------
 1 | [{"apple": "delicious"}, {"kiwi": "not-delicious"}]
 2 | [{"orange": "not-delicious"}, {"pair": "not-delicious"}]
 3 | [{"grapes": "delicious"}, {"strawberry": "not-delicious"}, {"carrot": "delicious"}]

Suppose this table is called tasks. While the keys in each of these objects are not predictable, the values are. For each row, I'd like to know the number of "delicious" and number of "not-delicious" values in the reviews array.

Edit for clarification:

I'm looking for the delicious/not-delicious counts for each id/row in the above table. Sample desired output:

id | delicious | not_delicious
-------------------------------
 1 |         1 |             1
 2 |         0 |             2
 3 |         2 |             1

标签： sqlpostgresqljsonb

Answer 1

lets say r is your table:

so=# select * from r;
                                       reviews
-------------------------------------------------------------------------------------
 [{"apple": "delicious"}, {"kiwi": "not-delicious"}]
 [{"orange": "not-delicious"}, {"pair": "not-delicious"}]
 [{"grapes": "delicious"}, {"strawberry": "not-delicious"}, {"carrot": "delicious"}]
(3 rows)

then:

so=# with j as (select jsonb_array_elements(reviews) a, r, ctid from r)
select jsonb_object_keys(a), a->>jsonb_object_keys(a),ctid from j;
 jsonb_object_keys |   ?column?    | ctid
-------------------+---------------+-------
 apple             | delicious     | (0,1)
 kiwi              | not-delicious | (0,1)
 orange            | not-delicious | (0,2)
 pair              | not-delicious | (0,2)
 grapes            | delicious     | (0,3)
 strawberry        | not-delicious | (0,3)
 carrot            | delicious     | (0,3)
(7 rows)

I used ctid as row identifier, cos I have no other column and did not want long reviews

and obviously aggregation of delicious per row:

so=# with j as (select jsonb_array_elements(reviews) a, r, ctid from r)
select ctid, a->>jsonb_object_keys(a), count(*) from j group by a->>jsonb_object_keys(a),ctid;
 ctid  |   ?column?    | count
-------+---------------+-------
 (0,1) | delicious     |     1
 (0,3) | delicious     |     2
 (0,1) | not-delicious |     1
 (0,2) | not-delicious |     2
 (0,3) | not-delicious |     1
(5 rows)

for updated post

so=# with j as (select jsonb_array_elements(reviews) a, r, ctid from r)
, n as (
 select ctid,a->>jsonb_object_keys(a) k from j
)
, ag as (
select ctid
, case when k = 'delicious' then 1 else 0 end deli
, case when k = 'not-delicious' then 1 else 0 end notdeli
from n
)
select ctid, sum(deli) deli, sum(notdeli) notdeli from ag group by ctid;
 ctid  | deli | notdeli
-------+------+---------
 (0,1) |    1 |       1
 (0,2) |    0 |       2
 (0,3) |    2 |       1
(3 rows)