db:Postgresql-14. To bude řídká transformace, a hledám doporučení / vylepšení, které mohou být vyrobeny tak jsem se může naučit/zdokonalit své postgres/json dovednosti (rychlost a/optimalizovat to velmi pomalé dotazu).
Dostáváme variabilní velikosti/struktury json objektů z externího rozhraní api.
Každý json objekt je průzkum reakci. Každý vnořený "dotaz/odpověď" objekt může mít docela jinou strukturu. Celkem existuje asi ~5 známých struktur.
Reakce objekty jsou uloženy v jsonb sloupec, který má jsonb_ops gin index.
Tabulka má asi 500.000 řádky. Každý řádek je jsonb sloupci objekt má o 200 vnořených hodnot.
Naším cílem je, aby extrahovat všechny vnořené otázka/odpověď odpovědi do další tabulky id,otázka,odpověď. V cílové tabulce budeme dělat rozsáhlé dotazování s FTS a trigram, a jsou zaměřené na schématu jednoduchost. To je důvod, proč jsem extrahování do jednoduché tabulky místo toho, aby dělal něco exotičtějšího s jsonb dotazování. Tam je také mnoho metadata cruft v ty objekty, které nepotřebuji. Tak jsem také doufat, že ušetřit nějaký prostor tím, archivace původu stolu (je to 5GB + indexy).
Konkrétně bych rád, aby se dozvědět více elegantní způsob, jak křížení a získávání json do cílové tabulky.
A já jsem byl schopen přijít na způsob, jak obsazení výsledky skutečné sql text místo citoval jsontext (normálně bych použít ->>, ::text, nebo _text verze jsonb funkce)
To je velmi zjednodušená verze json objekt zmírnit právě běží.
Děkuji předem!
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
NÁSLEDNÁ ÚPRAVA PO RAFINACI A DOSTAT VÝSLEDEK, KTERÝ JSEM POTŘEBOVAL
Toto je dotaz, jsem skončil. Trvalo 11min zpracovat a vložit 34million záznamy. Což je fajn, protože to je jeden čas operace.
Několik poznámek o změnách, které jsem udělal
-Použil jsem -> a ->> místo [subscripting], protože jsem četl, že i v pg14, subscripting nepoužívá indexy (nejsem si jistý, jestli na tom záleží v Z)
- "to_json(...) #>> '{}'" je, jak jsem se převede na řetězec json řetězec bez uvozovek na základě tohoto: přetečení zásobníku odpověď
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;
Konečné úpravy po přijetí níže odpověď jako řešení
Děkuji @Edouard H. odpověď a s lepší pochopení toho, jak správně používat jsonb_path_query, byl jsem schopen odstranit všechny UNION SELECT, objevit některé hodnoty, které chyběly, a odstranit potřebu pro to_json hack. I když CROSS JOIN LATERAL je implicitní s json funkcí, je lepší formě zahrnout JOIN místo čárky jako oni jsou více pevně vázán, a čitelnější. Níže je poslední dotaz, který jsem použil.
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)