JSON actual
{ "layout":"dynamicReport1", "templateType":"DYNAMIC_REPORTS", "containers":{ "fieldsContainer":[ { "fieldName":"role_id", "displayName":"Role", "fieldType":"text", "isHidden":true, "index":0, "queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#", "queryIdForParam":476 }, { "fieldName":"course_id", "displayName":"Course", "fieldType":"text", "isHidden":true, "index":1, "queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#", "queryIdForParam":477 }, { "fieldName":"location_id", "displayName":"Location", "fieldType":"text", "isHidden":true, "index":2, "queryForParam":"select name as location_id from location_master where id = #location_id#", "queryIdForParam":478 } ] } }La jerarquía es como
containers -> fieldContainer -> object Arriba está mi configuración json y quiero agregar queryUUIDForParam: random UUID a cada objeto a través de la consulta.
¿Cómo puedo insertar?
Intenté obtener una configuración actualizada con esta consulta, pero arroja un error:
select config::jsonb || ('{"queryUUIDForParam":' || cast(uuid as text) || '}')::jsonberror :
Error de SQL [22P02]: ERROR: sintaxis de entrada no válida para el tipo json Detalle: el token "5574ff23" no es válido. Donde: datos JSON, línea 1: {"queryUUIDForParam":5574ff23...
Mi resultado esperado es agregar el elemento "queryUUIDForParam" en Object Like. Quiero que se agregue un elemento con valor UUID. Este valor UUId se genera mediante una función aleatoria.
{ "layout":"dynamicReport1", "templateType":"DYNAMIC_REPORTS", "containers":{ "fieldsContainer":[ { "fieldName":"role_id", "displayName":"Role", "fieldType":"text", "isHidden":true, "index":0, "queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#", "queryIdForParam":476, "queryUUIDForParam":"1ea99f17-6965-4a0d-8d31-22b8777b9c62" }, { "fieldName":"course_id", "displayName":"Course", "fieldType":"text", "isHidden":true, "index":1, "queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#", "queryIdForParam":477, "queryUUIDForParam":"3ea99f17-6965-4a0d-8d31-22b8777b9c62" }, { "fieldName":"location_id", "displayName":"Location", "fieldType":"text", "isHidden":true, "index":2, "queryForParam":"select name as location_id from location_master where id = #location_id#", "queryIdForParam":478, "queryUUIDForParam":"9ea99f17-6965-4a0d-8d31-22b8777b9c62" } ] } }Gracias por adelantado :)
Prueba esto:
with cte as (select jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example), final_array as ( select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',(select uuid_generate_v4()))) "fieldsContainer" from cte )d) select jsonb_set( config::jsonb, '{containers,fieldsContainer}', (f.array_data),false) from example, final_array f;en caso de que quieras un uuid diferente para cada objeto
with cte as (select uuid_generate_v4() "uuid_",jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example), final_array as ( select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',uuid_)) "fieldsContainer" from cte )d) select jsonb_set( config::jsonb, '{containers,fieldsContainer}', (f.array_data),false) from example, final_array f;Nota : he usado la función incorporada de Postgres para generar el UUID. Ejecute la siguiente declaración antes de usarla
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";DEMO en DB-Violín