Cómo utilizar Postgres jsonb_path_query en lugar de seleccionar la unión

Question 1

db:Postgresql-14. Este va a ser un poco frecuentes transformación, y estoy buscando recomendaciones y mejoras que se pueden hacer para que yo pueda aprender/perfeccionar mi postgres/json habilidades (velocidad y/optimizar esta muy lento de la consulta).

Recibimos la variable de tamaño y estructura de objetos json de una api externa.

Cada objeto json es una respuesta de encuesta. Cada anidada "pregunta/respuesta" objeto puede tener una estructura diferente. En total hay alrededor de ~5 estructuras conocidas.

Respuesta objetos se almacenan en un jsonb columna que tiene un jsonb_ops gin índice.

La tabla tiene alrededor de 500.000 filas. Cada fila de la jsonb objeto de columna tiene alrededor de 200 anidada valores.

Nuestro objetivo es extraer todo el anidado de pregunta/respuesta en otra tabla de id,pregunta,respuesta. En la tabla de destino vamos a hacer una amplia consulta con FTS y trigrama, y se proponen para el esquema de la simplicidad. Es por eso que estoy extrayendo a una tabla simple en lugar de hacer algo más exótico con jsonb la consulta. También hay una gran cantidad de metadatos resto de los objetos que no necesito. Así que también estoy esperando para ahorrar algo de espacio al archivar la tabla de origen (es de 5GB + indexes).

En concreto me gustaría aprender una forma más elegante de atravesar y extraer el json a la tabla de destino.

Y he sido incapaz de encontrar una manera para emitir los resultados reales de texto sql lugar de la cita jsontext (normalmente yo uso ->>, ::texto, o el _textos versión de la jsonb función)

Esta es una versión muy simplificada del objeto json para facilitar la simple ejecución de este.

Gracias de antemano!

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

SEGUIMIENTO DE EDICIÓN DESPUÉS DE REFINACIÓN Y OBTENER EL RESULTADO QUE NECESITABA

Esta es la consulta que me terminó de correr. Tomó 11min a proceso e introducir 34million registros. Lo cual está bien ya que es una operación de tiempo.

Un par de comentarios acerca de los cambios que hice

-He utilizado -> y ->> en lugar de [suscripción] ya que he leído que incluso en pg14, suscripción no usar índices (no estoy seguro si lo que importa en el DE)
-la "to_json(...) #>> '{}'" es como me convertí en la cadena json a una cadena sin comillas basa en esto: desbordamiento de la pila de respuesta

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;

Última edición después de aceptar la respuesta como solución

Gracias a @Edouard H. respuesta y con una mejor comprensión de cómo utilizar correctamente los jsonb_path_query, yo era capaz de eliminar todas las UNION SELECT, descubrir algunos de los valores que había estado ausente, y eliminar la necesidad de la to_json hack. Aunque el CROSS JOIN LATERAL está implícito con json funciones, es la mejor forma para incluir JOIN en lugar de comas, ya que están más estrechamente vinculados, y más fácil de leer. A continuación es el final de la consulta que he usado.

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

Primera idea : reemplazar el 4 de consultas con UNION por 1 consulta única.

Segunda idea : la declaración de level1.value['answer'] as answer en la primera consulta suena como la declaración de jsonb_path_query(level1.value, '$.answer')::jsonb as answer en la segunda consulta. Creo que tanto las consultas devuelven el mismo conjunto de filas, y los duplicados son eliminados por el UNION entre ambas consultas.

Tercera idea : utilizar el jsonb_path_query función en el FROM la cláusula en lugar de la SELECT la cláusula, mediante CROSS JOIN LATERAL con el fin de romper el jsonb datos paso a paso :

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)

-- donde survey_id = 6633968 y id = 4

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

Primera idea : reemplazar el 4 de consultas con UNION por 1 consulta única.

Segunda idea : la declaración de level1.value['answer'] as answer en la primera consulta suena como la declaración de jsonb_path_query(level1.value, '$.answer')::jsonb as answer en la segunda consulta. Creo que tanto las consultas devuelven el mismo conjunto de filas, y los duplicados son eliminados por el UNION entre ambas consultas.

Tercera idea : utilizar el jsonb_path_query función en el FROM la cláusula en lugar de la SELECT la cláusula, mediante CROSS JOIN LATERAL con el fin de romper el jsonb datos paso a paso :

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)

-- donde survey_id = 6633968 y id = 4

Gracias por los comentarios. - Como lo que puedo decir, tengo la necesidad de unión, porque estoy recorrer todos los valores de los 4 diferentes estructurado de objetos json. - Buena captura, me perdí la que tenía de alguna manera se duplica. - json funciones incluidas en el implícitamente "lateral", por lo que no es necesario escribirlo (AFAIK) - para la #3, no pude conseguir que funcione. [42883] ERROR: la función jsonb_path_query(registro, desconocido) no existe Sugerencia: No función coincide con el nombre y tipos de argumentos. Usted puede ser que necesite para agregar explícita tipo de moldes.
Para #3 he actualizado la consulta, y espero que esto le funciona este momento con ningún error. En cuanto a la UNIÓN, todavía no entiendo por qué lo necesita y a qué te refieres por "4 diferentes estructurado de objetos json" ? Son diferentes columnas de la misma tabla, o de tablas diferentes ?
Tuve que hacer algunos cambios a lo que usted escribió para que funcione, pero lo más importante es que me llevó por el camino a una solución mucho mejor. Estás en lo correcto, mi falta de entendimiento acerca de jsonb_path_query significaba que estaba arreglando los sindicatos juntos. Para responder a tu pregunta, yo necesitaba valores de un par de claves diferentes para ser concat había junto a una columna. Como un bono, he encontrado un par de casos donde los valores de no ser capturado en mi consulta original. He editado el mensaje original con la solución final he utilizado. Gracias de nuevo.

Cómo utilizar Postgres jsonb_path_query en lugar de seleccionar la unión

Pregunta

SEGUIMIENTO DE EDICIÓN DESPUÉS DE REFINACIÓN Y OBTENER EL RESULTADO QUE NECESITABA

Un par de comentarios acerca de los cambios que hice

Última edición después de aceptar la respuesta como solución

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría