WITH config AS (
SELECT (WITH grados AS (
SELECT (elem1->>'id_grado')::INTEGER AS id_grado,
(elem1->>'id_libreta')::INTEGER AS id_libreta,
(SELECT JSONB_BUILD_OBJECT(
'areas', elem1->'config'->>'areas',
'firma1', elem1->'config'->>'firma1',
'firma2', elem1->'config'->>'firma2',
'is_logo', (elem1->'config'->>'is_logo')::BOOLEAN,
'is_escudo', (elem1->'config'->>'is_escudo')::BOOLEAN,
'conducta', (
SELECT JSONB_BUILD_OBJECT(
'active', (elem1->'config'->'conducta'->>'active')::BOOLEAN,
'detalle', (elem1->'config'->'conducta'->>'detalle')::BOOLEAN,
'leyenda', (elem1->'config'->'conducta'->>'leyenda')::BOOLEAN
)
),
'eva_ppff', (
SELECT JSONB_BUILD_OBJECT(
'active', (elem1->'config'->'eva_ppff'->>'active')::BOOLEAN,
'detalle', (elem1->'config'->'eva_ppff'->>'detalle')::BOOLEAN,
'leyenda', (elem1->'config'->'eva_ppff'->>'leyenda')::BOOLEAN
)
),
'detalle_asistencia', (
SELECT JSONB_BUILD_OBJECT(
'temprano', (elem1->'config'->'detalle_asistencia'->>'temprano')::BOOLEAN,
'tardanza', (elem1->'config'->'detalle_asistencia'->>'tardanza')::BOOLEAN,
'falta', (elem1->'config'->'detalle_asistencia'->>'falta')::BOOLEAN,
'tardanza_justificada', (elem1->'config'->'detalle_asistencia'->>'tardanza_justificada')::BOOLEAN,
'falta_justificada', (elem1->'config'->'detalle_asistencia'->>'falta_justificada')::BOOLEAN
)
),
'asistencia', (elem1->'config'->>'asistencia')::BOOLEAN,
'asignaturas', (elem1->'config'->>'asignaturas')::BOOLEAN,
'competencias', (elem1->'config'->>'competencias')::BOOLEAN,
'height_calif', (elem1->'config'->>'height_calif'),
'orden_merito', (elem1->'config'->>'orden_merito')::BOOLEAN,
'ocultar_prom_final', (elem1->'config'->>'ocultar_prom_final')::BOOLEAN,
'info_adicional', (
SELECT JSONB_BUILD_OBJECT(
'dre' , (SELECT JSONB_BUILD_OBJECT('desc', COALESCE((elem1->'config'->'info_adicional'->'dre'->>'desc'),'DRE'),'value', (elem1->'config'->'info_adicional'->'dre'->>'value'), 'active', (elem1->'config'->'info_adicional'->'dre'->>'active')::BOOLEAN)),
'ugel' , (SELECT JSONB_BUILD_OBJECT('desc', COALESCE((elem1->'config'->'info_adicional'->'ugel'->>'desc'),'UGEL'),'value', (elem1->'config'->'info_adicional'->'ugel'->>'value'), 'active', (elem1->'config'->'info_adicional'->'ugel'->>'active')::BOOLEAN)),
'cod_local' , (SELECT JSONB_BUILD_OBJECT('desc', COALESCE((elem1->'config'->'info_adicional'->'cod_local'->>'desc'),'Cód. Local'),'value', (elem1->'config'->'info_adicional'->'cod_local'->>'value'), 'active', (elem1->'config'->'info_adicional'->'cod_local'->>'active')::BOOLEAN)),
'cod_modular' , (SELECT JSONB_BUILD_OBJECT('desc', COALESCE((elem1->'config'->'info_adicional'->'cod_modular'->>'desc'),'Cód. Modular'),'value', (elem1->'config'->'info_adicional'->'cod_modular'->>'value'), 'active', (elem1->'config'->'info_adicional'->'info_adicional'->>'active')::BOOLEAN))
)
),
'padding_top_head', (elem1->'config'->>'padding_top_head'),
'cant_letras_x_fila', (elem1->'config'->>'cant_letras_x_fila')::INTEGER,
'orientacion', 'portrait',
'desc_reporte' , (elem1->'config'->>'desc_reporte'),
'color_template', (elem1->'config'->>'color_template'),
'desc_comentario', (elem1->'config'->>'desc_comentario'),
'ciclo', (elem1->'config'->>'ciclo')::BOOLEAN,
'replace_prof_final', COALESCE((elem1->'config'->>'replace_prof_final')::BOOLEAN, FALSE)
)) AS config
FROM JSONB_ARRAY_ELEMENTS(elem->'grados')
WITH ORDINALITY ARR(elem1, pos1)
)
SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB
FROM grados tab
) AS grados,
(elem->>'id_sede')::integer AS id_sede,
(elem->>'id_tipo_prog')::integer AS id_tipo_prog,
(elem->'watermark')::jsonb AS watermark
FROM JSONB_ARRAY_ELEMENTS((SELECT json_config FROM notas.config_notas WHERE id_config = 2))
WITH ORDINALITY ARR(elem, pos)
)
UPDATE notas.config_notas SET json_config = (SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB FROM config tab) WHERE id_config = 2;
UPDATE notas.libreta SET config = config || JSONB_BUILD_OBJECT('info_adicional', (
SELECT JSONB_BUILD_OBJECT(
'dre' , (SELECT JSONB_BUILD_OBJECT('desc', 'DRE','value', NULL, 'active', false)),
'ugel' , (SELECT JSONB_BUILD_OBJECT('desc', 'UGEL','value', NULL, 'active', false)),
'cod_local' , (SELECT JSONB_BUILD_OBJECT('desc', 'Cód. Local','value', NULL, 'active', false)),
'cod_modular' , (SELECT JSONB_BUILD_OBJECT('desc', 'Cód. Modular','value', NULL, 'active', false))
)
));
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.