Oracle - json data contains array/string interchangeably

Question

I have json data and I am trying to put that data in different columns in oracle. Issue is one of the column sometimes contains an array and sometimes contain string. I know there is different command to put json array to column but if the column is populated with string sometimes and array sometimes, how do I write sql so that it fetch all data -

SELECT id,array1
FROM   (
  select '{
    "data": [
      {
        "id": 1,
        "array1": [ "INFO", "ABC", ]
      },
      {
        "id": 2,
        "array1": "TEST",
      }
    ]
  }' AS JSON_DATA
  FROM DUAL
) I,
json_table(
  i.JSON_DATA ,
  '$.data[*]'  
  COLUMNS (
    array1 varchar2(4000) FORMAT JSON path'$."array1"',
    ID     varchar2(4000) path '$."id"'
  )  
) a

Output from the sql:

ID  ARRAY1
1   ["INFO","ABC"]
2

Desired Ouput :

ID  ARRAY1
1   ["INFO","ABC"]
2   TEST

Barbaros Özhan · Accepted Answer · 2020-01-03 16:35:13Z

2

array1 varchar2(4000) PATH '$."array1"' can be considered together with

array1 varchar2(4000) FORMAT JSON PATH '$."array1"'

Since both case exists for values of array1 key. So, use :

SELECT ID, NVL(array1, array1_) AS array1
  FROM   
  (
   SELECT '{
        "data": [

          {
            "id": 1,
            "array1": [ "INFO", "ABC" ]
          },
          {
            "id": 2,
            "array1": "TEST"
          }
        ]
      }' AS JSON_DATA
     FROM DUAL
     ) I
    CROSS JOIN 
    JSON_TABLE(
      i.JSON_DATA ,
      '$.data[*]'  
      COLUMNS (
           array1  varchar2(4000) PATH '$."array1"',
           array1_ varchar2(4000) FORMAT JSON PATH '$."array1"',
           ID      varchar2(4000) PATH '$."id"'
      )  
     ) A

Demo

answered Jan 3, 2020 at 16:35

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Comment

Pran Over a year ago

Thanks !! I was also thinking same. was wondering is there any alternate than to do nvl

Collectives™ on Stack Overflow

Oracle - json data contains array/string interchangeably

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related