Como usar Postgres jsonb_path_query em vez de selecionar união

Question 1

banco de dados:Postgresql-14. Isso vai ser um pouco frequentes transformação, e eu estou procurando recomendações de melhorias que podem ser feitas para que eu possa aprender/aprimorar meu postgres/json habilidades (e a velocidade/otimizar esta muito lento consulta).

Recebemos tamanho variável/estrutura de objetos json a partir de uma api externa.

Cada objeto json é uma resposta ao inquérito. Cada aninhados "pergunta/resposta" objeto pode ter uma forma bastante diferente da estrutura. No total, há cerca de ~5 conhecido estruturas.

Resposta de objetos são armazenadas em um jsonb coluna que tem um jsonb_ops gin índice.

A tabela tem cerca de 500.000 linhas. Cada linha do jsonb coluna de objeto tem cerca de 200 aninhadas valores.

Nosso objetivo é extrair todo o aninhadas perguntas/respostas respostas em outra tabela de identificação,pergunta,resposta. A tabela de destino vamos fazer uma extensa consulta com FTS e trigrama, e estão apontando para o esquema simplicidade. É por isso que eu estou extração para uma tabela simples em vez de fazer algo mais exótico, com jsonb a consultar. Há também um monte de metadados sujeira nesses objetos, que eu não preciso. Então, eu também estou esperando para poupar espaço, através do arquivo de origem (tabela é de 5GB + índices).

Especificamente, eu gostaria de saber uma forma mais elegante de atravessar e extrair o json para a tabela de destino.

E eu fui incapaz de descobrir uma maneira para lançar os resultados reais de texto sql em vez de aspas jsontext (normalmente eu uso>>, ::texto, ou _text versão do jsonb função)

Esta é uma versão muito simplificada do objeto json para facilidade executando apenas isso.

Obrigado antecipadamente!

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

ACOMPANHAMENTO EDITAR APÓS O REFINO E OBTER O RESULTADO QUE EU PRECISAVA

Esta é a consulta acabei de execução. Ele tomou 11min para processar e inserir 34million registros. O que é bom, como é um tempo de operação.

Alguns comentários sobre as alterações que eu fiz

-Eu usei -> e ->> em vez de [subscripting] desde que eu li que mesmo em pg14, subscripting não utilizar índices (não tenho certeza se o que importa a PARTIR de)
-o "to_json(...) #>> '{}'" é como eu me converti a seqüência de caracteres json para um cotadas cadeia de caracteres com base no presente: estouro de pilha resposta

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;

Edição Final, depois de receber abaixo de resposta como solução

Obrigado a @Edouard H. de resposta e com uma melhor compreensão de como usar corretamente jsonb_path_query, eu era capaz de eliminar todos os UNION SELECT, descobrir alguns valores que tinha sido falta, e remover a necessidade de to_json hack. Mesmo que o CROSS JOIN LATERAL está implícito com json funções, ele é melhor do formulário para incluir JOIN em vez de vírgulas, como eles são mais fortemente ligados, e mais fácil de ler. Abaixo é a última consulta que eu usei.

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

Primeira ideia : remplace a 4 consultas com UNION por 1 única consulta.

Segunda ideia : a instrução level1.value['answer'] as answer na primeira consulta soa como a declaração de jsonb_path_query(level1.value, '$.answer')::jsonb as answer na segunda consulta. Eu acho que ambas as consultas de retorno, o mesmo conjunto de linhas, e as duplicatas são removidas pelo UNION entre as duas consultas.

Terceira ideia : usar o jsonb_path_query função FROM a cláusula, em vez de o SELECT cláusula, usando CROSS JOIN LATERAL a fim de quebrar o jsonb dados passo a 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)

-- onde survey_id = 6633968 e id = 4

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

Primeira ideia : remplace a 4 consultas com UNION por 1 única consulta.

Segunda ideia : a instrução level1.value['answer'] as answer na primeira consulta soa como a declaração de jsonb_path_query(level1.value, '$.answer')::jsonb as answer na segunda consulta. Eu acho que ambas as consultas de retorno, o mesmo conjunto de linhas, e as duplicatas são removidas pelo UNION entre as duas consultas.

Terceira ideia : usar o jsonb_path_query função FROM a cláusula, em vez de o SELECT cláusula, usando CROSS JOIN LATERAL a fim de quebrar o jsonb dados passo a 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)

-- onde survey_id = 6633968 e id = 4

Obrigado pelo feedback. - Tanto quanto eu posso dizer, eu preciso de união, porque eu sou iterar através de todos os valores dos 4 diferentes estruturado de objetos json. - Bom de pegar, eu perdi o que eu tinha de alguma forma duplicada que. - json funções incluídas em a PARTIR de são implicitamente "lateral", por isso não é necessário escrevê-la (AFAIK) - para o #3, eu não poderia começar a funcionar. [42883] ERRO: função jsonb_path_query(record, desconhecido) não existe Dica: Não função corresponde com o nome e os tipos de argumento. Você poderá precisar adicionar explícita do tipo lança.
Para #3 eu atualizei a consulta, e esperamos que isso vai trabalha neste momento com nenhum erro. Em relação a UNIÃO, eu ainda não entendo por que você precisa e o que você quer dizer por "4 diferentes estruturado de objetos json" ? Eles são diferentes colunas da mesma tabela, ou de tabelas diferentes ?
Eu tive que fazer algumas edições para o que você escreveu para fazê-lo funcionar, mas o mais importante é você me levou para baixo o caminho para uma solução muito melhor. Você está correto, a minha falta de entendimento sobre jsonb_path_query significava que eu era calçada sindicatos juntos. Para responder a sua pergunta, eu precisava de valores provenientes de várias chaves para ser concat tinha junto a uma coluna. Como um bônus, encontrei alguns casos onde os valores não estavam sendo capturados em minha consulta original. Que eu tenha editado o lançamento original com a solução final que eu usei. Obrigado novamente.

Como usar Postgres jsonb_path_query em vez de selecionar união

Pergunta

ACOMPANHAMENTO EDITAR APÓS O REFINO E OBTER O RESULTADO QUE EU PRECISAVA

Alguns comentários sobre as alterações que eu fiz

Edição Final, depois de receber abaixo de resposta como solução

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria