วิธีใช้ Postgres jsonb_path_query แทนที่จะเลือกสหภาพ

db:Postgresql-ยุ 14 นี่จะเป็นกา infrequent การเปลี่ยนแปลงและฉันกำลังมองหาคำแนะนำของฉันแน่นอนด/ปรับปรุงที่สามารถสร้างดังนั้นฉันอาจจะเรียนรู้/องขัดเกลาของฉัน postgres/language ทักษะ(และความเร็ว/the result will be artificially smoothed to hide jpeg artefacts นี้มาเชื่องช้ากับการค้นหา).

เราได้รับตัวแปรขนาด/โครงสร้างของ language วัตถุจากเว็บเบราว์เซอร์ภายนอกรูปแบบ api.

แต่ละ language วัตถุเป็น survey การตอบสนองเลย แต่ละครอบการ"คำถาม/คำตอบ"สิ่งสามารถค่อนข้างแตกต่างโครงสร้างของ. ในทั้งหมดนั่นมันเกี่ยวกับ~5 รู้จักนั่นซิ

การตอบสนอวัตถุงจะถูกจัดเก็บไว้ใน jsonb คอลัมน์ที่จะมี jsonb_ops จิดัชนี

โต๊ะมีเรื่อง 500,000 แถว. แต่ละแถวเป็น jsonb คอลัมน์สิ่งมีเรื่อง 200 รอบการค่า.

เป้าหมายของเราคือต้องคลายแฟ้มทั้งหมดรอบการถามคำถาม/คำตอบฟื้นคืนชีพไปยังอีกโต๊ะของบัตรประชาชนคำถามตอบคำถาม ที่ปลายทางโต๊ะเราจะต้องทำด้ querying กับ FTS และ trigram และเราตั้งใจว่าจะขว้างตรงชุดรูปแบบรู้สึกอย่างนั้น. นั่นคือเหตุผลว่าทำไมข้าถึง extracting จะง่ายอย่างแทนที่จะเป็นโต๊ะของการทำอย่างอื่นนอกล้วนทำให้ตื่นเต้นกับ jsonb querying. มันยังเป็นมากมายของข้อมูลกำกับภาพ cruft ในพวกนั้นสิ่งที่ฉันไม่ต้องการ ดังนั้นฉันก็หวังว่าจะช่วยบางอวกาศโดยการรับจดหมายที่แหล่งกำเนิดโต๊ะ(มันเป็น 5GB+indexes).

โดยเฉพาะฉันอยากจะเรียนรู้มากกว่าแบบเรียบร้อยสวยงามทางของ traversing และ extracting ที่ language ไปที่ปลายทางโต๊ะ

และฉันไม่สามารถหาหนทางที่จะแสดงผลที่แท้จริงภาษา sql ข้อความแทนที่จะเป็นส่วนที่อ้างถึง jsontext(ตามปกติฉันจะใช้->>,::ข้อความหรือค _text เวอร์ชั่นของ jsonb ฟังก์ชัน)

นี่เป็นรุ่นพื้นฐานของ language สิ่งจะปล่อยอแค่วิ่งหนีเรื่องนี้

ขอบคุณล่วงหน้า!

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

ติดตามไปยัแก้ไขหลังจากปรับปรุงและได้ผลลัพธ์ฉันต้องการ

นี่คือกับการค้นหาฉันพบว่าฉันกำลังหนี มันต้องใช้ 11min ต้องโพรเซสและแท 34million รายการบันทึก ซึ่งไม่เป็นไรมันคือครั้งหนึ่งดำเนินงานการ)กิบัติงานอยู่

ไม่กี่ความคิดเห็นเรื่องการเปลี่ยนแปลงฉันทำ

-ฉันเคย->และ->>แทนที่จะเป็น[subscripting]ตั้งแต่ฉันอ่านว่าแม้แต่ใน pg14,subscripting ไม่ใช้ indexes(ไม่แน่ใจว่ามันสำคัญในกจาก)
-คน "to_json(...) #>> '{}'" คือว่าฉันแปลงที่ language ข้อความต้องการ unquoted ข้อความจากพื้นฐานนี่: งานเลยล้นมาถึงคำตอบ

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;

สุดท้ายแก้ไขหลังจากยอมรับด้านล่างตอบเป็นทางออก

ขอบคือต้อง@เอ็ดเวิลด์เอชตอบแล้วกับคดีความเข้าใจเกี่ยวกับวิธีใช้อย่างถูกต้อง jsonb_path_query ฉันสามารถที่จะกำจัดทั้งหมด UNION SELECTค้นพบบางอย่าค่าของที่หายไป,และลบต้องการสำหรับ to_json บบแฮ็คด้วย ถึงแม้ว่าค CROSS JOIN LATERAL คือที่แยกกำหนดกับ language ฟังก์ชันที่มันจะดีกว่าแบบฟอร์ต้องรวม JOIN แทนที่จะเป็น commas อย่างที่พวกเขาเป็นมากกผูกแน่นและง่ายที่จะอ่าน. ด้านล่างนี้เป็นคนสุดท้าย query ฉันเคย.

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)