Return jsonb as rows in Postgres

Question

I have a json column that holds an array of objects containing a 'name' attribute. I'm trying to build a query that can extract the unique names out of all the objects, across all rows.

Here is what the data looks like:

[{
  "date": "2022-06-14T12:51:24.424Z",
  "name": "review_1"
}]

[{
  "date": "2022-06-14T12:50:56.454Z",
  "name": "review_3"
}, {
  "date": "2022-06-14T12:51:10.695Z",
  "name": "review_6"
}]

[{
  "date": "2022-06-14T12:51:57.997Z",
  "name": "review_3"
}]

[{
  "date": "2022-06-14T12:52:17.442Z",
  "name": "review_1"
}, {
  "date": "2022-06-14T12:54:35.239Z",
  "name": "review_9
}]

My approach is to get all the names as individual rows like this so I can find the distincts.

name    ||  date
review_1    2022-06-14T12:51:24.424Z
review_3    2022-06-14T12:50:56.454Z
review_6    2022-06-14T12:51:10.695Z
review_3    2022-06-14T12:51:57.997Z
review_1    2022-06-14T12:52:17.442Z
review_9    2022-06-14T12:54:35.239Z

I've tried using jsonb_array_elements following this post postgres + jsonb + get values of key from multidimentional array but it looks like that is for multidimentional arrays and I cannot get it to work.

Please show us the query that you tried and the error message that you got. — Bergi
– Bergi, Commented Jun 14, 2022 at 20:56

klin · Accepted Answer · 2022-06-14 19:55:51Z

4

It is hard to guess what kind of problems you have encountered, but the use of the function seems quite simple.

select elem->>'name' as "name", elem->>'date' as "date"
from my_table
cross join jsonb_array_elements(json_col) as arr(elem);

Test it in Db<>fiddle.

answered Jun 14, 2022 at 19:55

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

moonshot Over a year ago

that's it! had a feeling jsonb_array_elements was the function but never used it in a cross join. thanks

Collectives™ on Stack Overflow

Return jsonb as rows in Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related