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,
'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
)
),
'is_escudo', (elem1->'config'->>'is_escudo')::BOOLEAN,
'asistencia', (elem1->'config'->>'asistencia')::BOOLEAN,
'asignaturas', (elem1->'config'->>'asignaturas')::BOOLEAN,
'orientacion', 'portrait',
'competencias', (elem1->'config'->>'competencias')::BOOLEAN,
'height_calif', (elem1->'config'->>'height_calif'),
'orden_merito', (elem1->'config'->>'orden_merito')::BOOLEAN,
'info_adicional', (
SELECT JSONB_BUILD_OBJECT(
'dre' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'ugel' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'cod_local' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE)),
'cod_modular' , (SELECT JSONB_BUILD_OBJECT('value', null, 'active', FALSE))
)
),
'padding_top_head', (elem1->'config'->>'padding_top_head'),
'cant_letras_x_fila', (elem1->'config'->>'cant_letras_x_fila')::INTEGER,
'ocultar_prom_final', (elem1->'config'->>'ocultar_prom_final')::BOOLEAN
)) 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
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;
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.