Как использовать Postgres jsonb_path_query вместо выбора объединения

Question 1

бд:Postgresql-14. Это будет нечастая трансформация, и я ищу рекомендации / улучшения, которые можно сделать, чтобы я мог изучить/отточить свои навыки postgres/json (и ускорить/оптимизировать этот очень медленный запрос).

Мы получаем объекты json переменного размера/структуры из внешнего api.

Каждый объект json является ответом на опрос. Каждый вложенный объект "вопрос/ответ" может иметь совершенно иную структуру. Всего известно около ~5 структур.

Объекты ответа хранятся в столбце jsonb с индексом jsonb_ops gin.

Таблица содержит около 500 000 строк. Объект столбца jsonb каждой строки содержит около 200 вложенных значений.

Наша цель состоит в том,чтобы извлечь все вложенные ответы на вопросы/ответы в другую таблицу идентификаторов,вопросов и ответов. В таблице назначения мы будем выполнять обширные запросы с помощью FTS и триграммы и стремимся к простоте схемы. Вот почему я извлекаю данные в простую таблицу вместо того, чтобы делать что-то более экзотическое с запросами jsonb. В этих объектах также много метаданных, которые мне не нужны. Поэтому я также надеюсь сэкономить немного места, архивируя исходную таблицу (это 5 ГБ + индексы).

В частности, я хотел бы изучить более элегантный способ обхода и извлечения json в таблицу назначения.

И я не смог найти способ привести результаты к фактическому тексту sql вместо jsontext в кавычках (обычно я бы использовал ->>>, ::текст или _текстовую версию функции jsonb)

Это очень упрощенная версия объекта json, чтобы упростить его выполнение.

Заранее благодарю вас!

create table test_survey_processing(
    id integer generated always as identity constraint test_survey_processing_pkey primary key,
    json_data jsonb
);
insert into test_survey_processing (json_data)
values ('{"survey_data": {"2": {"answer": "Option 1", "question": "radiobuttonquesiton"}, "3": {"options": {"10003": {"answer": "Option 1"}, "10004": {"answer": "Option 2"}}, "question": "checkboxquestion"}, "5": {"answer": "Column 2", "question": "Row 1"}, "6": {"answer": "Column 2", "question": "Row 2"}, "7": {"question": "checkboxGRIDquesiton", "subquestions": {"8": {"10007": {"answer": "Column 1", "question": "Row 1 : Column 1"}, "10008": {"answer": "Column 2", "question": "Row 1 : Column 2"}}, "9": {"10007": {"answer": "Column 1", "question": "Row 2 : Column 1"}, "10008": {"answer": "Column 2", "question": "Row 2 : Column 2"}}}}, "11": {"answer": "Option 1", "question": "Row 1"}, "12": {"answer": "Option 2", "question": "Row 2"}, "13": {"options": {"10011": {"answer": "Et molestias est opt", "option": "Option 1"}, "10012": {"answer": "Similique magnam min", "option": "Option 2"}}, "question": "textboxlist"}, "14": {"question": "textboxgridquesiton", "subquestions": {"15": {"10013": {"answer": "Qui error magna omni", "question": "Row 1 : Column 1"}, "10014": {"answer": "Est qui dolore dele", "question": "Row 1 : Column 2"}}, "16": {"10013": {"answer": "vident mol", "question": "Row 2 : Column 1"}, "10014": {"answer": "Consectetur dolor co", "question": "Row 2 : Column 2"}}}}, "17": {"question": "contactformquestion", "subquestions": {"18": {"answer": "Rafael", "question": "First Name"}, "19": {"answer": "Adams", "question": "Last Name"}}}, "33": {"question": "customgroupquestion", "subquestions": {"34": {"answer": "Sed magnam enim non", "question": "customgroupTEXTbox"}, "36": {"answer": "Option 2", "question": "customgroupradiobutton"}, "37": {"options": {"10021": {"answer": "Option 1", "option": "customgroupCHEC KBOX question : Option 1"}, "10022": {"answer": "Option 2", "option": "customgroupCHEC KBOX question : Option 2"}}, "question": "customgroupCHEC KBOX question"}}}, "38": {"question": "customTABLEquestion", "subquestions": {"10001": {"answer": "Option 1", "question": "customTABLEquestioncolumnRADIO"}, "10002": {"answer": "Option 2", "question": "customTABLEquestioncolumnRADIO"}, "10003": {"options": {"10029": {"answer": "OPTION1"}, "10030": {"answer": "OPTION2"}}, "question": "customTABLEquestioncolumnCHECKBOX"}, "10004": {"options": {"10029": {"answer": "OPTION1"}, "10030": {"answer": "OPTION2"}}, "question": "customTABLEquestioncolumnCHECKBOX"}, "10005": {"answer": "Aperiam itaque dolor", "question": "customTABLEquestioncolumnTEXTBOX"}, "10006": {"answer": "Hic qui numquam inci", "question": "customTABLEquestioncolumnTEXTBOX"}}}}}');
create index test_survey_processing_gin_index on test_survey_processing using gin (json_data);

-- the query I'm using (it works, but it is unmanageably slow)

-- EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON)
select level1.value['question'] question, level1.value['answer'] as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.options.*.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4
union
select level1.value['question'] question, jsonb_path_query(level1.value, '$.subquestions.*.*.answer')::jsonb as answer ,tgsr.json_data['survey_data']
from test_survey_processing tgsr,
     jsonb_each(tgsr.json_data['survey_data']::jsonb) level1
-- where survey_id = 6633968 and id = 4

ПОСЛЕДУЮЩЕЕ РЕДАКТИРОВАНИЕ ПОСЛЕ УТОЧНЕНИЯ И ПОЛУЧЕНИЯ НУЖНОГО МНЕ РЕЗУЛЬТАТА

Это запрос, который я в итоге запустил. На обработку и вставку 34 миллионов записей ушло 11 минут. Что прекрасно, так как это одноразовая операция.

Несколько комментариев по поводу внесенных мной изменений

-Я использовал -> и - > > > > > > вместо [подписки], так как я прочитал, что даже в pg14 подписка не использует индексы (не уверен, имеет ли это значение в ОТ)
-в "to_json(...) #>> '{}'" вот как я преобразовал строку json в строку без кавычек на основе этого: ответ на переполнение стека

create table respondent_questions_answers as
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question, '' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.answer')) #>> '{}' as answer 
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.options.*.option')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.options.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1 
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.*.question')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1
union
select tgsr.id,tgsr.survey_id,level1.value ->> 'question' question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.question')) #>> '{}' as sub_question,
       to_json(jsonb_path_query(level1.value, '$.subquestions.*.answer')) #>> '{}' as answer
from test_survey_processing tgsr, jsonb_each(tgsr.json -> 'survey_data') level1;

Окончательное редактирование после принятия приведенного ниже ответа в качестве решения

Спасибо @Edouard H. ответ и с лучшим пониманием того, как правильно использовать jsonb_path_query, я смог устранить все UNION SELECT, обнаружьте некоторые отсутствующие значения и устраните необходимость взлома to_json. Даже несмотря на то, что CROSS JOIN LATERAL неявно с функциями json, это лучшая форма для включения JOIN вместо запятых, так как они более плотно связаны и их легче читать. Ниже приведен последний запрос, который я использовал.

SELECT concat_ws(' ',
    qu.value::jsonb->>'question'
,   an.answer::jsonb->>'question'
,   an.answer::jsonb->>'option') AS question
,   an.answer::jsonb->>'answer' AS answer
--      , tgsr.json_data->>'survey_data'
FROM test_survey_processing tgsr
         CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu
         CROSS JOIN LATERAL jsonb_path_query(qu.value::jsonb, '$.** ? (exists(@.answer))') AS an(answer)

Question 2

Первая идея : перенесите 4 запроса с UNION по 1 уникальному запросу.

Вторая идея : утверждение level1.value['answer'] as answer в первом запросе звучит как утверждение jsonb_path_query(level1.value, '$.answer')::jsonb as answer во втором запросе. Я думаю, что оба запроса возвращают один и тот же набор строк, и дубликаты удаляются UNION между обоими запросами.

Третья идея : используйте jsonb_path_query функция в FROM пункт вместо SELECT пункт, используя CROSS JOIN LATERAL для того, чтобы разбить данные jsonb шаг за шагом :

SELECT qu.question->>'question' AS question
     , an.answer->>'answer' AS answer
     , tgsr.json_data->>'survey_data'
  FROM test_survey_processing tgsr
 CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu(question)
 CROSS JOIN LATERAL jsonb_path_query(qu.question, '$.** ? (exists(@.answer))') AS an(answer)

-- где survey_id = 6633968 и id = 4

Edouard · Answer 1 · 2021-11-24T19:50:54

Первая идея : перенесите 4 запроса с UNION по 1 уникальному запросу.

Вторая идея : утверждение level1.value['answer'] as answer в первом запросе звучит как утверждение jsonb_path_query(level1.value, '$.answer')::jsonb as answer во втором запросе. Я думаю, что оба запроса возвращают один и тот же набор строк, и дубликаты удаляются UNION между обоими запросами.

Третья идея : используйте jsonb_path_query функция в FROM пункт вместо SELECT пункт, используя CROSS JOIN LATERAL для того, чтобы разбить данные jsonb шаг за шагом :

SELECT qu.question->>'question' AS question
     , an.answer->>'answer' AS answer
     , tgsr.json_data->>'survey_data'
  FROM test_survey_processing tgsr
 CROSS JOIN LATERAL jsonb_each(tgsr.json_data->'survey_data') AS qu(question)
 CROSS JOIN LATERAL jsonb_path_query(qu.question, '$.** ? (exists(@.answer))') AS an(answer)

-- где survey_id = 6633968 и id = 4

Спасибо за отзыв. - Насколько я могу судить, мне нужно объединение, потому что я перебираю все значения 4 различных структурированных объектов json. - Хороший улов, я упустил, что каким-то образом продублировал это. - функции json, включенные в FROM, неявно "боковые", поэтому нет необходимости их выписывать (AFAIK) - для #3 я не смог заставить это работать. [42883] ОШИБКА: функция jsonb_path_query(запись, неизвестно) не существует Подсказка: Ни одна функция не соответствует заданному имени и типам аргументов. Возможно, вам потребуется добавить явные приведения типов.
Для #3 я обновил запрос и надеюсь, что на этот раз он сработает без ошибок. Что касается ОБЪЕДИНЕНИЯ, я все еще не понимаю, зачем вам это нужно и что вы подразумеваете под "4 различными структурированными объектами json" ? Являются ли они разными столбцами одной и той же таблицы или из разных таблиц ?
Мне пришлось внести несколько правок в то, что вы написали, чтобы это сработало, но самое главное, что вы повели меня по пути к гораздо лучшему решению. Вы правы, мое непонимание jsonb_path_query означало, что я собирал союзы вместе. Чтобы ответить на ваш вопрос, мне нужно было объединить значения из нескольких разных ключей в один столбец. В качестве бонуса я обнаружил несколько случаев, когда значения не фиксировались в моем исходном запросе. Я отредактировал исходную публикацию с помощью окончательного решения, которое я использовал. Еще раз спасибо.

Как использовать Postgres jsonb_path_query вместо выбора объединения

Вопрос

ПОСЛЕДУЮЩЕЕ РЕДАКТИРОВАНИЕ ПОСЛЕ УТОЧНЕНИЯ И ПОЛУЧЕНИЯ НУЖНОГО МНЕ РЕЗУЛЬТАТА

Несколько комментариев по поводу внесенных мной изменений

Окончательное редактирование после принятия приведенного ниже ответа в качестве решения

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории