Ako používať Postgres jsonb_path_query vyberte miesto únie

Question 1

db:Postgresql-14. Toto bude zriedkavé transformácie, a ja som pri pohľade na odporúčania / vylepšenia, ktoré môžu byť vyrobené tak som sa dozvedieť/zdokonaľovať moje postgres/json zručnosti (a rýchlosť/optimalizovať tento veľmi pomaly dotaz).

Budeme dostávať premennej veľkosť/štruktúra json objektov z externého api.

Každý json objekt je prieskum odpoveď. Každý vnorené "otázka/odpoveď" objekt môže mať úplne odlišné štruktúry. Celkom je tu o ~5 známych štruktúr.

Odpoveď objekty sú uložené v jsonb stĺpec, ktorý má jsonb_ops gin index.

Tabuľka má asi 500.000 riadkov. Každý riadok je jsonb stĺpec objekt má o 200 vnorené hodnoty.

Naším cieľom je získať všetky vnorené otázka/odpoveď reakcií do inej tabuľky id,otázka,odpoveď. Na cieľovej tabuľky budeme robiť rozsiahle prehľadávanie s FTS a trigram, a sú zamerané na schému jednoduchosť. To je dôvod, prečo som extrakcie do jednoduchej tabuľky, namiesto toho, aby niečo robiť viac exotických s jsonb dotaz. Tam je tiež veľa metadáta crufts v tých objektov, ktoré nepotrebujem. Tak som aj dúfal, že sa šetrí priestor archivácia pôvodu tabuľka (je to 5 GB + indexy).

Osobitne rád by som sa dozvedieť sa viac elegantný spôsob, ako kríženie a získavanie json k cieľovej tabuľky.

A ja som nebol schopný vymyslieť spôsob, ako odovzdať výsledky aktuálnych sql text namiesto kótované jsontext (normálne by som použiť ->>, ::text, alebo _text verzia jsonb funkcia)

Je to veľmi zjednodušená verzia json objektov zmierniť práve beží to.

Vopred ďakujeme!

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

NADVIAZANIE UPRAVIŤ PO RAFINÁCIE A DOSTAŤ VÝSLEDOK SOM POTREBOVAL

Toto je dotaz, skončil som beží. Trvalo to 11min na spracovanie a vložte 34million záznamy. Čo je v poriadku tak ako to je jeden čas prevádzky.

Niekoľko poznámok o zmeny, ktoré som urobil

-Použil som -> a ->> namiesto [subscripting], pretože som čítal, že aj v pg14, subscripting nepoužíva indexy (nie je istý, či to, že veciach, v Z)
-na "to_json(...) #>> '{}'" je to, ako som sa obrátil json reťazec do nekótovaných string založené na toto: pretečenie zásobníka odpoveď

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á úprava po prijatí nižšie odpoveď, ako riešenie

Ďakujeme, že je na @Edouard H. odpoveď a lepšie pochopenie ako správne používať jsonb_path_query, bol som schopný odstrániť všetky UNION SELECT, objavte niektoré hodnoty, ktoré boli chýbajúce, a odstrániť potrebu to_json hack. Aj keď sa CROSS JOIN LATERAL je implicitné s json funkcie, je lepšie formulár obsahovať JOIN namiesto čiarky, ako sú pevne viazané, a lepšie sa číta. Nižšie je konečný dopyt som 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)

Question 2

Prvá myšlienka : remplace 4 dotazov s UNION tým, 1 jedinečné dotaz.

Druhá myšlienka : vyhlásenie level1.value['answer'] as answer v prvom dotaz znie rovnako ako vyhlásenie jsonb_path_query(level1.value, '$.answer')::jsonb as answer v druhom dotaz. Myslím, že obe otázky vrátiť rovnaké riadky, a duplikáty sú odstránené UNION medzi obe otázky.

Tretia myšlienka : použiť jsonb_path_query funkcia v FROM ustanovenie namiesto SELECT doložka, pomocou CROSS JOIN LATERAL v záujme prelomenia jsonb údajov krok za krokom :

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)

- kde survey_id = 6633968 a id = 4

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

Prvá myšlienka : remplace 4 dotazov s UNION tým, 1 jedinečné dotaz.

Druhá myšlienka : vyhlásenie level1.value['answer'] as answer v prvom dotaz znie rovnako ako vyhlásenie jsonb_path_query(level1.value, '$.answer')::jsonb as answer v druhom dotaz. Myslím, že obe otázky vrátiť rovnaké riadky, a duplikáty sú odstránené UNION medzi obe otázky.

Tretia myšlienka : použiť jsonb_path_query funkcia v FROM ustanovenie namiesto SELECT doložka, pomocou CROSS JOIN LATERAL v záujme prelomenia jsonb údajov krok za krokom :

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)

- kde survey_id = 6633968 a id = 4

Vďaka za spätnú väzbu. - Ako ďaleko, ako len môžem povedať, potrebujem únie, pretože som iterating cez všetky hodnoty 4 rôzne štruktúrované json objektov. - Dobrý úlovok, chýbalo mi, že som sa nejako duplikované, že. - json funkcie zahrnuté v Z sú implicitne "bočné", tak to nie je potrebné písať to (pokiaľ viem) - pre #3, nemohla som si, že pracovať. [42883] CHYBA: funkcia jsonb_path_query(záznam, neznámy) neexistuje Poznámka: Žiadna funkcia zodpovedá meno a argument typy. Možno budete musieť pridať explicitný typ odliatkov.
Pre #3 aktualizoval som dotaz a dúfam, že to bude funguje tento čas bez chyby. O ÚNIE, ja stále neviem pochopiť, prečo ju potrebujete a čo myslíš tým "4 rôzne štruktúrované json objektov" ? Sú rôzne rubriky v rovnakom stole, alebo z rôznych tabuliek ?
Musel som urobiť niekoľko úprav, aby to, čo si napísal, aby to fungovalo, ale najdôležitejšie je, že ste ma viedol po ceste k oveľa lepšie riešenie. Máte pravdu, môj nedostatok vedomostí o jsonb_path_query znamenalo som bol cobbling odbory spolu. Ak chcete odpovedať na vaše otázky, potreboval som hodnôt z niekoľkých rôznych kľúče concat by spolu na jednom stĺpci. Ako bonus, som zistil niekoľko prípadov, keď hodnoty neboli zachytená v mojej pôvodnej otázke. Som upravených pôvodného vysielania s konečným riešením, ktoré som použil. Ešte raz vďaka.

Ako používať Postgres jsonb_path_query vyberte miesto únie

Otázka

NADVIAZANIE UPRAVIŤ PO RAFINÁCIE A DOSTAŤ VÝSLEDOK SOM POTREBOVAL

Niekoľko poznámok o zmeny, ktoré som urobil

Konečná úprava po prijatí nižšie odpoveď, ako riešenie

Najlepšiu odpoveď

V iných jazykoch

Táto stránka je v iných jazykoch

Populárne v tejto kategórii

Obľúbené otázky v tejto kategórii