Create mulitple records from array-of-arrays within Postgres JSON object

Question

Within my Postgres database, I have a column (type=JSONB) that contains an API response that represents someone response to a Survey Monkey survey. Within this JSON object, there is an array called pages that contains at least 1 item, and something more than 1 item. Then within pages, there is another array called questions. This array also contains at least 1 item, and more often more than 1 item.

I am trying to write a Postgres query to extract the responses from all the questions seen within pages and questions. The final format should be 1 record per question. If the survey has 5 questions, that would create 5 rows. If the survey has 100 questions, across 5 pages, that would mean 100 rows.

+────────────+────────────────────────────────────────────+──────────────────────+
| id         | heading                                    | simple_text          |
+────────────+────────────────────────────────────────────+──────────────────────+
| 775249149  | I am confident that I am the best.         | Somewhat Agree       |
| 775249153  | I currently have the skills to be amazing  | Somewhat Agree       |
| 775249166  | How long until I win the lottery?          | 6 to 12 months more  |
+────────────+────────────────────────────────────────────+──────────────────────+

I've been trying to use the function json_array_elements, but I'm experiencing some errors when trying to pass in an array-of-arrays.

{
    "id": "12345",
    "href": "https://api.surveymonkey.com",
    "pages": [
        {
            "id": "142250690",
            "questions": [
                {
                    "id": "775249149",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514018",
                            "choice_id": "5133514023",
                            "simple_text": "Somewhat Agree",
                            "choice_metadata": {
                                "weight": "5"
                            }
                        }
                    ],
                    "heading": "I am confident that I am the best.",
                    "subtype": "rating"
                },
                {
                    "id": "775249153",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514112",
                            "choice_id": "5133514117",
                            "simple_text": "Somewhat Agree",
                            "choice_metadata": {
                                "weight": "5"
                            }
                        }
                    ],
                    "heading": "I currently have the skills to be amazing",
                    "subtype": "rating"
                },
                {
                    "id": "775249166",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514278",
                            "choice_id": "5133514280",
                            "simple_text": "6 to 12 months more",
                            "choice_metadata": {
                                "weight": "2"
                            }
                        }
                    ],
                    "heading": "How long until I win the lottery?",
                    "subtype": "rating"
                }
            ]
        }
    ],
    "survey_id": "123456789",
    "total_time": 29,
    "date_created": "2022-07-25T23:08:36+00:00",
    "recipient_id": "",
    "date_modified": "2022-07-25T23:09:06+00:00",
    "email_address": "",
    "collection_mode": "default",
    "response_status": "completed",
}

Nick · Accepted Answer · 2022-07-26 18:52:24Z

Try to use json_array_elements() twice:

http://sqlfiddle.com/#!17/9eecb/94455

select v->'id', v->'heading', v->'subtype', v->'answers'->0->'simple_text' from (
  select json_array_elements(value->'questions')::jsonb as v from json_array_elements('{
    "id": "12345",
    "href": "https://api.surveymonkey.com",
    "pages": [
        {
            "id": "142250690",
            "questions": [
                {
                    "id": "775249149",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514018",
                            "choice_id": "5133514023",
                            "simple_text": "Somewhat Agree",
                            "choice_metadata": {
                                "weight": "5"
                            }
                        }
                    ],
                    "heading": "I am confident that I am the best.",
                    "subtype": "rating"
                },
                {
                    "id": "775249153",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514112",
                            "choice_id": "5133514117",
                            "simple_text": "Somewhat Agree",
                            "choice_metadata": {
                                "weight": "5"
                            }
                        }
                    ],
                    "heading": "I currently have the skills to be amazing",
                    "subtype": "rating"
                },
                {
                    "id": "775249166",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514278",
                            "choice_id": "5133514280",
                            "simple_text": "6 to 12 months more",
                            "choice_metadata": {
                                "weight": "2"
                            }
                        }
                    ],
                    "heading": "How long until I win the lottery?",
                    "subtype": "rating"
                }
            ]
        },
        {
            "id": "142250690",
            "questions": [
                {
                    "id": "775249199",
                    "family": "matrix",
                    "answers": [
                        {
                            "row_id": "5133514278",
                            "choice_id": "5133514280",
                            "simple_text": "6 to 12 months more",
                            "choice_metadata": {
                                "weight": "2"
                            }
                        }
                    ],
                    "heading": "qweqweqweqwqqqq?",
                    "subtype": "rating"
                }
            ]
        }
    ],
    "survey_id": "123456789",
    "total_time": 29,
    "date_created": "2022-07-25T23:08:36+00:00",
    "recipient_id": "",
    "date_modified": "2022-07-25T23:09:06+00:00",
    "email_address": "",
    "collection_mode": "default",
    "response_status": "completed"
  }'::jsonb::json->'pages')
) t;

UPDATE

If you need to process data from table contained described json rows, you need something like this:

http://sqlfiddle.com/#!17/16b65/1

I tried to edit your query to point to my JSON column within my table: select v->'id', v->'heading', v->'subtype', v->'answers'->0->'simple_text' from surveymonkey.responses_raw rr, ( select json_array_elements(value->'questions')::jsonb as v from json_array_elements(response::json -> 'pages')) t;, but I'm not getting this error: ERROR: column "response" does not exists. Hint: There is a column named "response" in the table "rr", but it cannot be referenced from this part of the query

Collectives™ on Stack Overflow

Create mulitple records from array-of-arrays within Postgres JSON object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related