0

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:

Like This

What's wrong with my query?

Thank you so much.

5
  • How does the input and output relate? What happened with values 4and 5 of municipioid? Commented Dec 13, 2020 at 22:40
  • the output is just an example of how I need the structure. Commented Dec 13, 2020 at 22:50
  • You would need to show us the actual output you want for this specific data. Otherwise it is rather unclear what exactly you are after. Commented Dec 13, 2020 at 22:57
  • I just need those 2 arrays to turn into 2 columns.. Commented Dec 13, 2020 at 23:03
  • You can't do that with JSON functions alone - for the trivial reason that JSON structure assigns no meaning to array values of separate attributes. The biggest issue is your input data, not your query. If those values were supposed to be related as you are showing in your output, the JSON should be an array of objects, each with the two attributes you have at the top level in your JSON. The way you have your data now, you will need to extract the array values to columns in two separate subqueries, and then join by ordinality. Commented Dec 13, 2020 at 23:06

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

Comments

1

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.