Postgresql: How to get values from arrays of JSONB?

Question

We have official documentation which unclear for me without examples.

I have JSON loke this structure, its a bit bigger but the same structure, more JSONs inside the array:

WITH
  responses AS (
      SELECT *
      FROM (VALUES ((1,
        '[
          {
            "id": "13",
            "alias": "r1",
            "title": "quest",
            "answer": "5",
            "question": "qq",
            "answer_id": 10048
          },
          {
            "id": "24",
            "alias": "q6",
            "title": "quest",
            "answer": "yes",
            "question": "quest",
            "answer_id": 10094
          }
        ]' :: JSON),
        (2,
        '[
          {
            "id": "13",
            "alias": "r1",
            "title": "quest",
            "answer": "-1",
            "question": "qq",
            "answer_id": 10048
          },
          {
            "id": "24",
            "alias": "q6",
            "title": "quest",
            "answer": "no",
            "question": "quest",
            "answer_id": 10094
          }
        ]' :: JSON))



           )    TEST(id,val)

  )
  SELECT * from responses

I could transform to flat structure:

id| question| answer
-----------
1, 'r1', 5
1, 'q6', yes
2, 'r1', -1
2, 'q6', no

But I need to get result in this way

id| r1| q6
----------
1,  5, yes

2, -1, no

How can I get result like last one?

See https://stackoverflow.com/a/23061730/50552 for how to turn rows into columns — Andomar, Mar 16 '18 at 15:06

Nick · Accepted Answer · 2018-03-16T15:22:57.783

There are some issues with your WITH query.

First, there is no need to do "select * from (values (..))" – VALUES is itself a query which you can use without SELECT:

test=# values (1, 'zz'), (2, 'bbb');
 column1 | column2
---------+---------
       1 | zz
       2 | bbb
(2 rows)

Next, there is an issue with parentheses. Compare the previous query with this one:

test=# values ((1, 'zz'), (2, 'bbb'));
 column1 | column2
---------+---------
 (1,zz)  | (2,bbb)
(1 row)

If you place additional parentheses like this, you got just one row of two columns, and values in these columns being of "anonymous" record type (its Postgres type magic, very powerful, but it's different story, not needed here).

So let's fix the first part of your CTE:

with responses(id, val) AS (
 values
  (1, '
    [
      {
        "id": "13",
        "alias": "r1",
        "title": "quest",
        "answer": "5",
        "question": "qq",
        "answer_id": 10048
      },
      {
        "id": "24",
        "alias": "q6",
        "title": "quest",
        "answer": "yes",
        "question": "quest",
        "answer_id": 10094
      }
    ]'::json
  ), (2, '
    [
      {
        "id": "13",
        "alias": "r1",
        "title": "quest",
        "answer": "-1",
        "question": "qq",
        "answer_id": 10048
      },
      {
        "id": "24",
        "alias": "q6",
        "title": "quest",
        "answer": "no",
        "question": "quest",
        "answer_id": 10094
      }
    ]'::json
  )
)
select * from responses;

Now we can use json_array_elements(..) function to extract JSON elements from the JSON arrays:

with responses(id, val) AS (
 values
 ...
)
select id, json_array_elements(val)
from responses;

Let's use it to construct the "second stage" of our CTE:

...
), extra(id, elem) as (
  select id, json_array_elements(val)
  from responses
)
...

And finally, we'll get the needed result like this:

...
select
  id,
  elem->>'id' as json_id,
  elem->>'alias' as alias,
  elem->>'question' as question,
  elem->>'answer' as answer
from extra;

Entire query:

with responses(id, val) AS (
 values
  (1, '
    [
      {
        "id": "13",
        "alias": "r1",
        "title": "quest",
        "answer": "5",
        "question": "qq",
        "answer_id": 10048
      },
      {
        "id": "24",
        "alias": "q6",
        "title": "quest",
        "answer": "yes",
        "question": "quest",
        "answer_id": 10094
      }
    ]'::json
  ), (2, '
    [
      {
        "id": "13",
        "alias": "r1",
        "title": "quest",
        "answer": "-1",
        "question": "qq",
        "answer_id": 10048
      },
      {
        "id": "24",
        "alias": "q6",
        "title": "quest",
        "answer": "no",
        "question": "quest",
        "answer_id": 10094
      }
    ]'::json
  )
), extra(id, elem) as (
  select id, json_array_elements(val)
  from responses
)
select
  id as row_id,
  elem->>'id' as json_id,
  elem->>'alias' as alias,
  elem->>'question' as question,
  elem->>'answer' as answer
from extra;

Result:

 row_id | id | alias | question | answer
--------+----+-------+----------+--------
      1 | 13 | r1    | qq       | 5
      1 | 24 | q6    | quest    | yes
      2 | 13 | r1    | qq       | -1
      2 | 24 | q6    | quest    | no
(4 rows)

This is a bit different from what you wanted. What you wanted cannot be achieved with pure SQL since you want to have dynamic column names in the output -- those "r1" and "q6" must be extracted from JSON data dynamically. It's possible with plpgsql though, or with tablefunc extension to create a pivoted table, let me know if you need it.

are you it cant be achieved without pivot? Getting column name is not problem, can be hardcoded? — Rocketq, Mar 20 '18 at 13:54
Sure if you can hardcode column names, you can do this easily with regular sql. — Nick, Mar 20 '18 at 14:54
With. But without extensions https://tapoueh.org/blog/2013/07/simple-case-for-pivoting-in-sql/ — Nick, Mar 20 '18 at 15:07

Postgresql: How to get values from arrays of JSONB?

1 Answers1