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

Vao Tsun · Accepted Answer · 2018-05-17 15:35:52Z

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)

edited May 17, 2018 at 15:35

answered May 17, 2018 at 15:22

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user94154 Over a year ago

I'll clarify my question, since this isn't quite what I'm looking for. Apologies, but thank you for this response.

Vao Tsun Over a year ago

@user94154 I got other result numbers, but they look correct to me

user94154 Over a year ago

Thanks for the update and catching my error. I updated my post to fix that.

Vao Tsun Over a year ago

if the answer acceptable, please accept and/or upvote it

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related