Cum să utilizați Postgres jsonb_path_query în loc de a selecta uniunii

Question 1

db:Postgresql-14. Aceasta va fi o rare de transformare, și eu sunt în căutarea pentru recomandări / îmbunătățiri care pot fi făcute astfel încât să pot învăța/perfecționa mea postgres/json abilitățile (și viteza/optimiza acest foarte lent interogare).

Vom primi variabila dimensiunea/structura obiecte json de la un api extern.

Fiecare obiect json este un sondaj de răspuns. Fiecare imbricate "întrebare/răspuns" obiect poate avea o structură destul de diferită. În total sunt aproximativ ~5 cunoscute structuri.

Răspuns obiecte sunt stocate într-un jsonb coloana care are o jsonb_ops gin index.

Masa a aproximativ 500.000 de rânduri. Fiecare rând e jsonb coloana obiect are aproximativ 200 de valori imbricate.

Scopul nostru este de a extrage toate imbricate întrebare/răspuns răspunsuri într-un alt tabel de identitate,întrebare,răspuns. Pe de destinație tabel vom face extinsă interogarea cu FTS și trigrama, și sunt menite pentru schema simplitate. De aceea extrag la un tabel simplu în loc de a face ceva mai exotic cu jsonb interogarea. Există, de asemenea, o mulțime de metadate cruft în acele obiecte de care nu am nevoie. Deci, eu sunt, de asemenea, speranța de a salva un spațiu de arhivare originea masă (e 5GB + indici).

În mod special mi-ar plăcea să învețe un mod mai elegant de traversare și extragerea json la destinație masă.

Și am fost în imposibilitatea de a figura o modalitate de a arunca rezultatele efective sql text în loc de citat jsontext (în mod normal, aș folosi ->>, ::text, sau _text versiune a jsonb funcția)

Aceasta este o versiune foarte simplificată a obiect json pentru a ușura doar rulează acest lucru.

Va multumesc anticipat!

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

URMARIRE EDITA DUPĂ RAFINARE ȘI DE A OBȚINE REZULTATUL AVEAM NEVOIE

Acest lucru este interogarea am ajuns de funcționare. A luat 11min pentru a procesa și de a introduce 34million înregistrări. Ceea ce este bine, deoarece este un timp de funcționare.

Câteva comentarii cu privire la modificările le-am făcut

-Am folosit -> si>> în loc de [subscripting] când am citit că, chiar și în pg14, subscripting nu utilizați indici (nu sunt sigur dacă asta contează în DE)
-la "to_json(...) #>> '{}'" este cum m-am convertit json string la un necotate șir bazat pe acest lucru: stack overflow răspunde

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;

Editarea finală după acceptarea de mai jos răspunsul soluție

Vă mulțumim pentru @Edouard H. răspuns și cu o mai bună înțelegere a modului de a utiliza în mod corect jsonb_path_query, am fost capabil de a elimina toate UNION SELECT, descoperi unele valori care au fost lipsă, și a elimina nevoia pentru to_json hack. Chiar dacă CROSS JOIN LATERAL este implicită cu json funcții, este o formă mai bună pentru a include JOIN în loc de virgule ca ele sunt mult mai strâns legate, și mai ușor de citit. Mai jos este ultima interogare-am folosit.

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

Prima idee : remplace 4 interogări cu UNION de 1 unic de interogare.

A doua idee : declarația level1.value['answer'] as answer în prima interogare sună ca o declarație jsonb_path_query(level1.value, '$.answer')::jsonb as answer în cea de-a doua interogare. Cred că ambele interogări de a reveni la același set de rânduri, și duplicatele sunt eliminate prin UNION între cele două interogări.

A treia idee : de a folosi jsonb_path_query funcția în FROM clauza în loc de SELECT clauza, folosind CROSS JOIN LATERAL în scopul de a rupe în jos jsonb date pas cu pas :

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)

- unde survey_id = 6633968 și id = 4

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

Prima idee : remplace 4 interogări cu UNION de 1 unic de interogare.

A doua idee : declarația level1.value['answer'] as answer în prima interogare sună ca o declarație jsonb_path_query(level1.value, '$.answer')::jsonb as answer în cea de-a doua interogare. Cred că ambele interogări de a reveni la același set de rânduri, și duplicatele sunt eliminate prin UNION între cele două interogări.

A treia idee : de a folosi jsonb_path_query funcția în FROM clauza în loc de SELECT clauza, folosind CROSS JOIN LATERAL în scopul de a rupe în jos jsonb date pas cu pas :

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)

- unde survey_id = 6633968 și id = 4

Multumesc pentru feedback-ul. - Cât de departe pot spune, am nevoie de uniune, pentru că sunt iterarea prin toate valorile 4 structurat diferite obiecte json. - Captură bun, am ratat asta am avut cumva duplicat asta. - json funcții incluse în LA sunt implicit "laterală", așa că nu este necesar să scrie (AFAIK) - pentru #3, nu am putut ajunge la serviciu. [42883] EROARE: funcția jsonb_path_query(înregistrare, necunoscut) nu există Indiciu: Nici o funcție meciuri dat nume și tipuri de argumente. S-ar putea nevoie pentru a adăuga explicit tipul de mulaje.
Pentru #3 am actualizat interogare, și sper că acest lucru va merge cu nici o eroare. În ceea ce privește UNIUNEA, eu încă nu înțeleg de ce ai nevoie și ce vrei să spui prin "4 structurat diferite obiecte json" ? Sunt diferite coloane de aceeași masă, sau din diferite tabele ?
Am avut de a face câteva modificări la ceea ce ai scris pentru a face să funcționeze, dar cel mai important m-ai dus în jos pe calea spre o soluție mult mai bună. Ai dreptate, lipsa mea de înțelegere cu privire la jsonb_path_query a însemnat am fost cobbling sindicate împreună. Să-ți răspund la întrebare, am nevoie de valori de la câteva chei diferite pentru a fi concat fi împreună pentru o singură coloană. Ca un bonus, am găsit câteva cazuri în care valorile nu au fost capturate în original interogare. Am editat original postarea cu soluția finală-am folosit. Multumesc din nou.

Cum să utilizați Postgres jsonb_path_query în loc de a selecta uniunii

Problema

URMARIRE EDITA DUPĂ RAFINARE ȘI DE A OBȚINE REZULTATUL AVEAM NEVOIE

Câteva comentarii cu privire la modificările le-am făcut

Editarea finală după acceptarea de mai jos răspunsul soluție

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie