Comment utiliser Postgres jsonb_path_query, au lieu de sélectionner l'union

Question 1

base de données:Postgresql-14. Ce sera un des rares transformation, et je suis à la recherche pour obtenir des recommandations et des améliorations qui peuvent être apportées afin que je puisse apprendre et de parfaire mes postgres/json compétences (et de la vitesse/optimiser cette très lente de la requête).

Nous recevons de taille variable/structure des objets json à partir d'un api externe.

Chaque objet json est une réponse à l'enquête. Chaque imbriquée "question/réponse" objet peut avoir une tout autre structure. Au total, il ya environ 5 structures connues.

Réponse objets sont stockés dans un jsonb colonne qui a une jsonb_ops gin index.

Table a environ 500 000 lignes. Chaque ligne du jsonb objet de colonne a environ 200 imbriquée valeurs.

Notre objectif est d'extraire tous les imbriquée question/réponse les réponses dans une autre table de id,question,réponse. Sur la table de destination que nous allons faire une vaste interrogation avec FTS et trigramme, et visent le schéma de la simplicité. C'est pourquoi je suis extraction à une table simple au lieu de faire quelque chose de plus exotique, avec des jsonb de l'interrogation. Il y a aussi beaucoup de métadonnées de fichiers inutiles dans ces objets que je n'ai pas besoin. Donc, je suis aussi dans l'espoir de sauver de l'espace par l'archivage de la table d'origine (c'est 5 GO + index).

Plus précisément, j'aimerais en apprendre un moyen plus élégant de la traversée et de l'extraction de l'json à la table de destination.

Et j'ai été incapable de trouver un moyen de jeter les résultats réels de texte sql au lieu de la cité jsontext (normalement, je voudrais utiliser ->>, ::texte, ou le _text version de la jsonb fonction)

C'est une version très simplifiée de l'objet json à l'aise tout de l'exécution de cette.

Je vous remercie à l'avance!

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

SUIVI DE MODIFIER APRÈS RAFFINAGE ET D'OBTENIR LE RÉSULTAT QUE J'AI BESOIN

C'est la requête j'ai terminé la course à pied. Il a fallu à 11min, et l'insérer 34million enregistrements. Ce qui est bien, car c'est un temps de l'opération.

Quelques commentaires sur les modifications que j'ai apportées

-J'ai utilisé -> et>> au lieu de [subscripting] depuis que j'ai lu que même dans pg14, subscripting ne pas utiliser d'index (pas sûr si ce qui compte dans la à PARTIR de)
-la "to_json(...) #>> '{}'" est comment j'ai converti la chaîne json pour un non cotées de la chaîne sur cette base: débordement de pile répondre

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;

Montage Final, après acceptation-dessous de la réponse comme solution

Merci à @Edouard H. réponse et avec une meilleure compréhension de la façon d'utiliser correctement jsonb_path_query, j'ai été en mesure d'éliminer tous les UNION SELECT, découvrez quelques valeurs qui avaient disparu, et de supprimer la nécessité pour le to_json hack. Même si l' CROSS JOIN LATERAL est implicite avec json fonctions, il est préférable pour inclure des JOIN au lieu de virgules, car ils sont plus étroitement liés, et plus facile à lire. Ci-dessous est la dernière question que j'ai utilisé.

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

Première idée : remplacer les 4 requêtes avec UNION en 1 requête unique.

Deuxième idée : la déclaration level1.value['answer'] as answer dans la première requête sonne comme la déclaration jsonb_path_query(level1.value, '$.answer')::jsonb as answer dans la deuxième requête. Je pense que les deux requêtes retournent le même ensemble de lignes, et les doublons sont supprimés par l' UNION entre les deux requêtes.

Troisième idée : utiliser le jsonb_path_query fonction dans l' FROM clause de la place de la SELECT clause, à l'aide de CROSS JOIN LATERAL afin de briser la jsonb données étape par étape :

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)

- où survey_id = 6633968 et id = 4

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

Première idée : remplacer les 4 requêtes avec UNION en 1 requête unique.

Deuxième idée : la déclaration level1.value['answer'] as answer dans la première requête sonne comme la déclaration jsonb_path_query(level1.value, '$.answer')::jsonb as answer dans la deuxième requête. Je pense que les deux requêtes retournent le même ensemble de lignes, et les doublons sont supprimés par l' UNION entre les deux requêtes.

Troisième idée : utiliser le jsonb_path_query fonction dans l' FROM clause de la place de la SELECT clause, à l'aide de CROSS JOIN LATERAL afin de briser la jsonb données étape par étape :

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)

- où survey_id = 6633968 et id = 4

Merci pour les commentaires. - Aussi loin que je peux dire, j'ai besoin de l'union, parce que je suis une itération à travers toutes les valeurs des 4 différents structuré objets json. - Bonne prise, j'en ai oublié que j'avais en quelque sorte double que. - json fonctions incluses dans le DE sont implicitement "latérale", de sorte qu'il n'est pas nécessaire de l'écrire (autant que je sache) - pour le #3, je ne pouvais pas obtenir que cela fonctionne. [42883] ERREUR: la fonction jsonb_path_query(enregistrement, inconnu) il n'existe pas de Soupçon: Pas de fonction correspond au nom donné et les types d'argument. Vous pourriez avoir besoin d'ajouter de type explicite jette.
Pour le #3 j'ai mis à jour la requête, et espérer que cela fonctionne cette fois sans erreur. Au sujet de l'UNION, je ne comprends toujours pas pourquoi vous en avez besoin et qu'entendez-vous par "4 structuré objets json" ? Sont-ils différents des colonnes de la même table, ou de de différentes tables ?
J'ai dû faire quelques modifications à ce que vous avez écrit pour le faire fonctionner, mais plus important encore, vous m'a conduit sur le chemin à une bien meilleure solution. Vous avez raison, mon manque de compréhension au sujet de jsonb_path_query signifiait que j'étais rassembler les syndicats. Pour répondre à votre question, j'ai besoin de valeurs de quelques clés différentes pour être concat serions ensemble pour une colonne. En bonus, j'ai trouvé quelques cas où les valeurs n'ont pas été capturés dans ma requête d'origine. J'ai édité le post d'origine avec la dernière solution que j'ai utilisée. Merci encore.

Comment utiliser Postgres jsonb_path_query, au lieu de sélectionner l'union

La question

SUIVI DE MODIFIER APRÈS RAFFINAGE ET D'OBTENIR LE RÉSULTAT QUE J'AI BESOIN

Quelques commentaires sur les modifications que j'ai apportées

Montage Final, après acceptation-dessous de la réponse comme solution

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie