db:Postgresql-14. यह हो जाएगा एक अनूठा परिवर्तन है, और मैं देख रहा हूँ के लिए सिफारिशें / सुधार किया जा सकता है कि तो मैं सीख सकते हैं/सान मेरे postgres/json कौशल (और गति/अनुकूलन यह बहुत ही धीमी गति से क्वेरी).
हम प्राप्त चर आकार/संरचना json वस्तुओं में से एक बाहरी एपीआई.
प्रत्येक json वस्तु है, एक सर्वेक्षण का जवाब. प्रत्येक नेस्ट "प्रश्न/उत्तर" वस्तु हो सकता है एक काफी अलग संरचना है । कुल में वहाँ के बारे में ~5 जाना जाता है संरचनाओं.
प्रतिक्रिया वस्तुओं को संग्रहित कर रहे हैं में एक jsonb स्तंभ है कि एक jsonb_ops जिन सूचकांक.
तालिका के बारे में 500.000 पंक्तियों. प्रत्येक पंक्ति के jsonb स्तंभ वस्तु के बारे में 200 नेस्टेड मानों.
हमारा लक्ष्य है निकालने के लिए सभी नेस्टेड सवाल/जवाब के जवाब में एक और टेबल की आईडी,सवाल है,जवाब है । गंतव्य पर टेबल पर हम क्या कर सकता हूँ व्यापक क्वेरी के साथ FTS और trigram है, और लक्ष्य कर रहे हैं के लिए स्कीमा सादगी. यही कारण है कि मैं कर रहा हूँ निकालने के लिए, एक साधारण तालिका करने के बजाय कुछ भी अधिक विदेशी के साथ jsonb क्वैरी. वहाँ भी है एक बहुत कुछ के मेटाडाटा cruft में उन वस्तुओं है कि मैं की जरूरत नहीं है. तो मैं भी उम्मीद कर रहा करने के लिए कुछ स्थान बचाने के संग्रह से मूल तालिका (यह 5GB + अनुक्रमित).
विशेष रूप से मैं प्यार करने के लिए जानने के लिए, एक और अधिक सुरुचिपूर्ण के रास्ते से गुजर और निकालने json गंतव्य के लिए टेबल.
और मैं में असमर्थ किया गया है करने के लिए एक तरह से बाहर आंकड़ा करने के लिए कलाकारों के लिए परिणाम वास्तविक sql पाठ के बजाय उद्धृत jsontext (आम तौर पर मैं का उपयोग करें चाहते हैं ->>, ::पाठ, या _text संस्करण के 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
FOLLOW-UP संपादित शोधन के बाद हो रही है और परिणाम मैं जरूरत
इस क्वेरी मैं समाप्त चल रहा है । यह ले लिया 11min करने के लिए प्रक्रिया और सम्मिलित 34million रिकॉर्ड. जो ठीक है के रूप में यह है एक बार आपरेशन ।
कुछ टिप्पणी के बारे में परिवर्तन मैंने बनाया
-मैं इस्तेमाल किया -> और>> के बजाय [subscripting] के बाद से मैं पढ़ा है कि यहां तक कि में pg14, subscripting का उपयोग नहीं करता अनुक्रमित (अगर यकीन नहीं है कि मामलों में से)
-के "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)