Блог

Главная - Вопросы по программированию - Ошибка SQL при попытке извлечь вложенные данные json

Ошибка SQL при попытке извлечь вложенные данные json

#sql #arrays #json #postgresql #unnest

#sql #массивы #json #postgresql #unnest

Вопрос:

Я запускаю SQL-запрос для извлечения вложенных данных JSON.

 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;

Сбой с сообщением Ошибка SQL [22023]: ОШИБКА: не удается извлечь элементы из объекта

Я использую PostgresSQL 11 . Вот как выглядит JSON,

 [
  {
    "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"
  }
]

Комментарии:

1. Пожалуйста, покажите нам результат, который вы хотите для этого образца данных.

2. Мне интересно, почему мой SQL-запрос не выполняется. SQL-запрос должен успешно вернуться с данными в моем редакторе SQL. В нем будет пять столбцов (responseId, chatId, data, text, uiActionCode). В частности, столбец данных должен содержать все данные внутри data : {} ; текст должен иметь «testQuestion2» в качестве значения в своем столбце, а uiActionCode будет иметь TextWithButton .

Ответ №1:

Если я правильно следую этому, достаточно одного уровня неинвестирования. Затем вы можете использовать средства доступа JSON для получения желаемых результатов:

 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'

Обратите внимание, что существует важное различие между средствами доступа -> и ->> . Первый возвращает объект, а второй возвращает текстовое значение. Вам нужно тщательно выбрать правильный оператор в соответствии с тем, что нужно сделать для каждого поля.