를 사용하는 방법 Postgres jsonb_path_query 대신에 선택 union

db:Postgresql-14. 이는 자주 변환,그리고 내가 찾는 것에 대한 권고 사항/개선할 수 있는 그래서 나는 것을 배울 수 있/연마 내 postgres/json 기술(과 속도/을 최적화하이에 매우 느린 쿼리).

우리가 받을 변수 크기/구조 json 체에서 외부 api 를 사용합니다.

각 json 개체입니다 설문 조사 응답입니다. 각각의 중첩"는 질문/답"객체가 아주 다른 구조입니다. 총 있에 대한~5 알려져 있는 구조입니다.

응답체에 저장되는 대화는 두서없는 열가 jsonb_ops 진 index.

테이블의 약 500,000 행이 있습니다. 각 행의 대화는 두서없이 열 개체에 대해 200 중첩된 값입니다.

우리의 목표를 추출하는 모든 중첩된 질문/답변 응답으로 다른 테이블의 id,질문에 대답합니다. 대상 테이블에서 우리가 하고 있을 것이다 광범위한 쿼리로젝트 및 건며,목표로 하는 스키마 단순합니다. 그 이유는 내가 추출하는 간단한 테이블 대신의 일을 아무 것도 더 이국적인 대화는 두서없이 쿼리. 또한 많은 메타데이터의 요구에서는 개체가 필요가 없습니다. 그래서 나는 또한 기대하고 저장 공간 보관하여 원본 테이블(그 5GB+indexes).

특히 나는 사랑을 배우고 더 많은 우아한 방법으로의 이동 및 추출하는 json 대상이다.

그리고 나는 못하는 방법을 파악하는 캐스트 결과를 실제 sql 텍스트 대신을 인용 jsontext(일반적으로 난 사>>,::텍스트,또는 _text 버전의 대화는 두서없능)

이것은 매우 간단한 버전의 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

편집 후속후 정제과 결과요

이것은 쿼리했 실행됩니다. 했 11 분 프로세스를 삽입 34million 기록합니다. 괜찮으로 그것은 한 시간 작업입니다.

몇에 대한 의견을 변경한 내용

-사용->과->>대[subscripting]이후로 읽는 pg14,subscripting 사용하지 않는 인덱스(확실하지 않는 경우는 문제서부터)
-이 "to_json(...) #>> '{}'" 은 어떻게 변환 json 문자열을 따옴표로 묶여 있지 않은 문자열에 따라 이: stack overflow 응답

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 은 암시된 기능,그것은 더 나은 형태를 포함 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)