Postgres JSONB concat object values in array

Question

I have a table

create table if not exists documents
(
    id   uuid  not null primary key,
    data jsonb not null
);

Column data is json like this

{
  "id": "e6cbb1d2-247d-4633-b9cf-5b33b14b032c",
  "books": [
    {
      "selected": false,
      "number": 1,
      "name": "First book",
      "description": {
        "note": "Some note for 1st book"
      }
    },
    {
      "selected": true,
      "number": 2,
      "name": "Second book",
      "description": {
        "note": "Some note for 2nd book"
      }
    },
    {
      "selected": false,
      "number": 3,
      "name": "Third book",
      "description": {
        "note": "Some note for 3rd book"
      }
    },
    {
      "selected": true,
      "number": 4,
      "name": "Fourth book",
      "description": {
        "note": "Some note for 4th book"
      }
    }
  ]
}

I need to fetch from each json object information about all selected books as one line in the following form: "number + name + description.note, number + name + description.note".

For the example above, the result should be something like this: "2 Second book Some note for 2nd book, 4 Fourth book Some note for 4th book".

I tried to use jsonb_path_query_array(), but I was only able to extract specific fields.

Answer 1

You can use the jsonb_to_recordset and jsonb_array_elements functions to shred it into rows.

SELECT
  d.id,
  b.selected,
  b.number,
  b.name,
  b.description->>'note' AS note
FROM documents d
CROSS JOIN jsonb_to_recordset(d.data->'books')
  AS b(selected bool, number int, name text, description jsonb);

You can then just do normal string_agg on that.

SELECT
  string_agg(concat_ws(' ', b.number, b.name, b.description->>'note'), ', ') AS result
FROM documents d
CROSS JOIN jsonb_to_recordset(d.data->'books')
  AS b(selected bool, number int, name text, description jsonb);

db<>fiddle