By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( id, json ) AS
SELECT 1, '{"tipodenunciaid":[1,2],"municipioid":[1,2]}' FROM DUAL UNION ALL
SELECT 2, '{"tipodenunciaid":[],"municipioid":[3,4]}' FROM DUAL UNION ALL
SELECT 3, '{"tipodenunciaid":[5],"municipioid":[]}' FROM DUAL UNION ALL
SELECT 4, '{"tipodenunciaid":[6,7],"municipioid":[6]}' FROM DUAL UNION ALL
SELECT 5, '{"tipodenunciaid":[8],"municipioid":[8,9]}' FROM DUAL;
5 rows affected
SELECT j.id,
CASE
WHEN t.id = m.id OR t.id > m.max_id OR m.id IS NULL
THEN tipodenunciaid
END AS tipodenunciaid,
CASE
WHEN t.id = m.id OR m.id > t.max_id OR t.id IS NULL
THEN municipioid
END AS municipioid
FROM table_name j
OUTER APPLY (
SELECT id,
tipodenunciaid,
MAX(id) OVER () AS max_id
FROM JSON_TABLE(
j.json,
'$.tipodenunciaid[*]'
COLUMNS (
id FOR ORDINALITY,
tipodenunciaid integer PATH '$'
)
)
) t
OUTER APPLY (
SELECT id,
municipioid,
MAX(id) OVER () AS max_id
FROM JSON_TABLE(
j.json,
'$.municipioid[*]'
COLUMNS (
id FOR ORDINALITY,
municipioid integer PATH '$'
)
)
) m
| ID | TIPODENUNCIAID | MUNICIPIOID |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 2 | null | 3 |
| 2 | null | 4 |
| 3 | 5 | null |
| 4 | 6 | 6 |
| 4 | 7 | null |
| 5 | 8 | 8 |
| 5 | null | 9 |