Oracle: JSON object members inside CASE expression?

Viewed 71

I'm trying to assemble a JSON document with mixed types like [["array"],{"data":"object"},"string"] from a conditional expression.

If I put only JSON types inside, Oracle somehow surmises that I'm composing them inside the outer document:

with t as (
    select 'array'  data from dual union ALL
    select 'object'      from dual union all
    select 'string'      from dual
)
select json_arrayagg(
  case data
    when 'array'  then json_array(data)
    when 'object' then json_object('key' value data)
  end json
)
from t
/

JSON
------------------------------------------------
[["array"],{"data":"object"}]

As soon as I add a string to the mix, the results of the CASE expression are all treated as strings and encoded accordingly:

with t as (
    select 'array'  data from dual union ALL
    select 'object'      from dual union all
    select 'string'      from dual
)
select json_arrayagg(
  case data
    when 'array'  then json_array(data)
    when 'object' then json_object('key' value data)
    else data
  end
)
from t
/

JSON
------------------------------------------------
["[\"array\"]","{\"data\":\"object\"}","string"]

But if I tell json_arrayagg to treat it as JSON, the last string member is not handled, resulting in an invalid document:

with t as (
    select 'array'  data from dual union ALL
    select 'object'      from dual union all
    select 'string'      from dual
)
select json_arrayagg(
  case data
    when 'array'  then json_array(data)
    when 'object' then json_object('data' value data)
    else data
  end FORMAT JSON
) json
from t

JSON
------------------------------------------------
[["array"],{"data":"object"},string]

I can't find any function like json_string to JSONify varchar2, and I'd prefer not to do it by hand.

SQL> select banner_full from v$version ;

BANNER_FULL
--------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
0 Answers
Related