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 stackoverflow.com/a/23061730/50552 for how to turn rows into columns — Andomar
– Andomar, Commented Mar 16, 2018 at 15:06

Nick · Accepted Answer · 2018-03-16 15:22:57Z

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?
Sure if you can hardcode column names, you can do this easily with regular sql.
With. But without extensions tapoueh.org/blog/2013/07/simple-case-for-pivoting-in-sql

Collectives™ on Stack Overflow

Postgresql: How to get values from arrays of JSONB?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related