Come usare Postgres jsonb_path_query invece di selezionare unione

Question 1

db:Postgresql-14. Questa sarà una rara trasformazione, e sto cercando consigli / miglioramenti che possono essere fatti in modo che io possa imparare/perfezionare il mio postgres/json abilità (e la velocità/ottimizzare questo molto lento query).

Riceviamo variabile dimensioni/struttura di oggetti json da un api esterna.

Ogni oggetto json è una risposta al sondaggio. Ogni nidificata "domanda/risposta" oggetto può avere un diverso struttura. In totale ci sono circa ~5 strutture note.

Risposta gli oggetti vengono memorizzati in un jsonb colonna jsonb_ops gin indice.

Tabella ha circa 500.000 righe. Ogni riga del jsonb colonna oggetto ha circa 200 valori annidati.

Il nostro obiettivo è quello di estrarre tutti i nidificati domanda/risposta risposte in un'altra tabella di id,domanda,risposta. La tabella di destinazione faremo una vasta interrogazione con FTS e trigrammi, e sta puntando dello schema di semplicità. Che è il motivo per cui io sono l'estrazione di una tabella semplice invece di fare qualcosa di più esotico con jsonb l'esecuzione di query. C'è anche un sacco di metadati cruft in quegli oggetti che non ho bisogno. Così sto anche sperando di risparmiare un po di spazio di archiviazione origine tabella (5GB + indici).

In particolare mi piacerebbe imparare un modo più elegante di attraversamento e di estrazione di json per la tabella di destinazione.

E sono stato in grado di capire un modo per lanciare i risultati effettivi di testo sql invece del citato jsontext (normalmente mi piacerebbe usare ->>, ::testo, o il _text versione del jsonb funzione)

Questa è una versione semplificata dell'oggetto json per facilitare l'esecuzione di questo.

Vi ringrazio in anticipo!

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 MODIFICA DOPO LA RAFFINAZIONE E DI OTTENERE IL RISULTATO DI CUI AVEVO BISOGNO

Questa è la query che ho finito la corsa. Ci sono voluti 11min di elaborare ed inserire 34million record. Che è un bene, in quanto è uno per volta.

Alcuni commenti sulle modifiche che ho fatto

-Ho usato -> e ->> invece di [subscripting] da quando ho letto che anche in pg14, subscripting non utilizzare gli indici (non sono sicuro se che conta nell')
-il "to_json(...) #>> '{}'" è come ho convertito la stringa json per un non quotate stringa basata su questo: stack overflow risposta

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;

Modifica finale dopo aver accettato sotto la risposta come soluzione

Grazie a @Edouard H. risposta e con una migliore comprensione di come utilizzare correttamente jsonb_path_query, sono stato in grado di eliminare tutti i UNION SELECT, scoprire alcuni dei valori che mancava, e rimuovere la necessità per il to_json hack. Anche se il CROSS JOIN LATERAL è implicita con json funzioni, è la forma migliore per includere JOIN al posto delle virgole in quanto sono più strettamente legati, e di più facile lettura. Qui di seguito è l'ultima domanda che ho utilizzato.

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 idea : remplace le 4 query con UNION da 1 unica query.

Seconda idea : la dichiarazione di level1.value['answer'] as answer nella prima query suona come l'istruzione jsonb_path_query(level1.value, '$.answer')::jsonb as answer nella seconda query. Penso che sia la query restituiscono lo stesso set di righe, e i duplicati vengono rimossi dal UNION tra le due query.

Terza idea : utilizzare il jsonb_path_query funzione in FROM clausola invece di SELECT clausola, utilizzando CROSS JOIN LATERAL al fine di abbattere il jsonb dati passo per passo :

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)

-- dove survey_id = 6633968 e id = 4

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

Prima idea : remplace le 4 query con UNION da 1 unica query.

Seconda idea : la dichiarazione di level1.value['answer'] as answer nella prima query suona come l'istruzione jsonb_path_query(level1.value, '$.answer')::jsonb as answer nella seconda query. Penso che sia la query restituiscono lo stesso set di righe, e i duplicati vengono rimossi dal UNION tra le due query.

Terza idea : utilizzare il jsonb_path_query funzione in FROM clausola invece di SELECT clausola, utilizzando CROSS JOIN LATERAL al fine di abbattere il jsonb dati passo per passo :

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)

-- dove survey_id = 6633968 e id = 4

Grazie per il feedback. - Per quanto posso dire, ho bisogno di unione, perché io sono di scorrere tutti i valori dei 4 diversi strutturato di oggetti json. - Buona cattura, ho perso che avevo in qualche modo ripetuto che. - json funzioni sono implicitamente "laterale", quindi non è necessario scrivere fuori (AFAIK) - per la #3, non ho potuto ottenere che per lavoro. [42883] ERRORE: funzione jsonb_path_query(record, sconosciuto) non esiste Hint: No funzione corrisponde al nome e tipi di argomenti. Potrebbe essere necessario aggiungere esplicito il tipo di cast.
Per #3 ho aggiornato la query, e spero che questo sarà questa volta funziona con nessun errore. Per quanto riguarda l'UNIONE, non ho ancora capito a che cosa serve e che cosa si intende per "4 diversi strutturato di oggetti json" ? Sono diverse le colonne della stessa tabella o tabelle diverse ?
Ho dovuto fare alcune modifiche a quello che hai scritto per farlo funzionare, ma soprattutto mi ha portato giù il percorso di una soluzione molto migliore. Hai ragione, la mia mancanza di comprensione circa jsonb_path_query significava che ero cobbling sindacati insieme. Per rispondere alla tua domanda, avevo bisogno di valori, da un paio di chiavi diverse per essere concat piacerebbe insieme a una colonna. Come bonus, ho trovato un paio di casi in cui i valori non erano stati catturati nella mia query originale. Ho modificato il post originale con la soluzione finale che ho usato. Grazie di nuovo.

Come usare Postgres jsonb_path_query invece di selezionare unione

Domanda

FOLLOW-UP MODIFICA DOPO LA RAFFINAZIONE E DI OTTENERE IL RISULTATO DI CUI AVEVO BISOGNO

Alcuni commenti sulle modifiche che ho fatto

Modifica finale dopo aver accettato sotto la risposta come soluzione

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria