SQL Error while trying to extract nested json data

Question

I am running an SQL query to extract nested JSON data.

SELECT watsonResponse.responseId,
       watsonResponse.chatId,
       d.*
FROM watson_response_table watsonResponse
         CROSS JOIN LATERAL (
    SELECT d2.*
    FROM jsonb_array_elements(watsonResponse.watsonresponse_output) AS d(events)
             CROSS JOIN LATERAL (
        SELECT d2.events ->> 'data'         AS watsonResponse_ouput_data
             , d2.events ->> 'text'         AS watsonResponse_output_text
             , d2.events ->> 'uiActionCode' AS watsonResponse_output_uiActionCode
        FROM jsonb_array_elements(d.events) AS d2(events)
        ) d2
    WHERE d.events ->> 'uiActionCode' = 'TextWithButton'
    ) d;

It fails with message SQL Error [22023]: ERROR: cannot extract elements from an object

I am using PostgresSQL 11+. Here is what the JSON looks like,

[
  {
    "text": [
      "Some text!"
    ],
    "uiActionCode": "textOnly"
  },
  {
    "data": {
      "type": "options",
      "options": [
        { "label": "test", "value": "testvalue" },
        { "label": "test2", "value": "testvalue2" },
        {
          "label": "test3",
          "value": "testQuestion?"
        }
      ]
    },
    "text": ["testQuestion2?"],
    "uiActionCode": "TextWithButton"
  }
]

Please show us the result that you want for this sample data. — GMB
– GMB, Commented Oct 26, 2020 at 17:22
I'm wondering why my SQL query is failing. The sql query should return successfully with the data in my SQL editor. It will have five columns (responseId, chatId, data, text, uiActionCode). Specifically, data column should have everything inside of data : {} ; text should have "testQuestion2" as the value in its column, and uiActionCode will have TextWithButton. — cluis92
– cluis92, Commented Oct 26, 2020 at 17:42

GMB · Accepted Answer · 2020-10-26 17:46:24Z

1

If I am following this correctly, one level of unnesting is sufficient. You can then use the JSON accessors to get the results you want:

SELECT 
    r.responseId,
    r.chatId,
    d.events ->> 'uiActionCode' AS output_uiActionCode,
    d.events -> 'text' ->> 0    AS output_text,
    d.events -> 'data'          AS output_data,
FROM watson_response_table watsonResponse r
CROSS JOIN LATERAL jsonb_array_elements(r.watsonresponse_output) AS d(events)
WHERE d.events ->> 'uiActionCode' = 'TextWithButton'

Note that there is an important difference between accessors -> and ->>. The former returns an object, while the latter returns a text value. You need to carefully pick the correct operator according to what needs to be done for each field.

answered Oct 26, 2020 at 17:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Error while trying to extract nested json data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related