Return jsonb as rows in Postgres-CodePudding

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.

CodePudding user response：

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.