Question 89171 in PostgreSQL Question posted in
The official documentation can be found here.

Using postgres to break JSON column into Question and Answer columns? – Postgresql

I have a large JSON column that I would like to break into two columns with multiple records instead of a separate column for each component. Example of the way data looks below.

Data Json
1 [{"Q1":"A1", "Q2":"A2", "Q3":"A3"}]
2 [{"Q1":"A4", "Q2":"A5", "Q3":"A6"}]

I can break it down easily into separate columns named for each question, but for some reason am hitting a block at finding out the right way to break it into just two columns. Example of structure I am looking to create below.

Data Question Answer
1 Q1 A1
1 Q2 A2
1 Q3 A3
2 Q1 A4
2 Q2 A5
2 Q3 A6
Tags:

2

Answers


  1. 0

    This is a perfect use case for the json_each function:

    SELECT data, key AS question, value AS answer
FROM example, json_each(json->0)

    Or if there’s any number of objects in the array, combine with json_array_elements:

    SELECT e.data, pair.key AS question, pair.value AS answer
FROM example e, json_array_elements(e.json) AS item, json_each(item.value) AS pair
    Login or Signup to reply.
  2. 0

    Just to add to @Bergi, probably you would want to use json_each_text since your values are known to be string:

    select data, key as Question, value as Answer 
from example, json_each_text(json->0);
    data question answer
    1 Q1 A1
    1 Q2 A2
    1 Q3 A3
    2 Q1 A4
    2 Q2 A5
    2 Q3 A6
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search