ORACLE JSON_TABLE I need to get 2 columns from an array

Question

I have this SQL code

SELECT * FROM JSON_TABLE (('
[
    "tipodenunciaid": [1,2],
    "municipioid": [1,2]
]
'), '$[*]' COLUMNS (tipodenunciaid integer PATH '$.tipodenunciaid[*]', municipioid integer PATH '$.municipioid[*]'));

And I want to get the next result:

What's wrong with my query?

Thank you so much.

Answer 1

You can't use json_table() like that. If you want both arrays unnested in different columns, and lined up according to the position of each element in each array, then one option would be to separately expand each array to rows, using ordinality to keep track of the position of each element, then join the two resultsets. A full join comes handy to accommodate for an unequal number of elements in each array:

with 
    data as (
        select '{ "tipodenunciaid": [1,2,3,4], "municipioid": [1,2,3,4,5] }' as js from dual
    ),
    t as (
        select t.*
        from data d
        cross apply json_table(
            d.js,
            '$.tipodenunciaid[*]' columns (rn for ordinality, tipodenunciaid integer path '$')
        ) t
    ),
    m as (
        select m.*
        from data d
        cross apply json_table(
            d.js,
            '$.municipioid[*]' columns (rn for ordinality, municipioid integer path '$')
        ) m
    )
select t.tipodenunciaid, m.municipioid
from t
full join m on m.rn = t.rn

Demo on DB Fiddle.

Answer 2

You can use two OUTER APPLYs with FOR ORDINALITY to generate the ordinates of the array to connect the table to itself:

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
WHERE  t.id = m.id
OR     t.id IS NULL
OR     ( m.id > t.max_id AND t.id = t.max_id )
OR     m.id IS NULL
OR     ( t.id > m.max_id AND m.id = m.max_id );

Which, for the sample data:

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;

Outputs:

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

db<>fiddle here