Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Amit.

Asked: June 01, 2022 - 7:50 pm UTC

Last updated: June 30, 2022 - 7:51 am UTC

Version: Oracle 19

Viewed 10K+ times! This question is

You Asked

Hi Team,
Any idea, why generic path expression based renaming of JSON fields does not work?

A sample-
1. Below function transform top layer, but leaves nested layer.
Select 
JSON_TRANSFORM('{"field_a": 2, "field_b": 3 , "nested" : {"nested1":{"field_a":2, "field_b":3 }}}', 
RENAME '$..field_a'='field_c') 
from DUAL;

Output: {"field_b":3,"nested":{"nested1":{"field_a":2,"field_b":3}},"field_c":2}

2. While below can be alternate, but practically impossible with complex documents.

Select 
JSON_TRANSFORM('{"field_a": 2, "field_b": 3 , "nested" : {"nested1":{"field_a":2, "field_b":3 }}}', 
RENAME '$.field_a'='field_c',
RENAME '$.nested.nested1.field_a'='field_c')
from DUAL;


Output: {"field_b":3,"nested":{"nested1":{"field_b":3,"field_c":2}},"field_c":2}


and Chris said...

It's not supported by JSON_TRANSFORM.

You can use wildcards for the path to an attribute, so you can have a rename for each level of nesting.

For example:

select json_transform (
  '{"field_a": 2, "field_b": 3 , "nested" : {"nested1":{"field_a":2, "field_b":3 }}}', 
  rename '$.field_a' = 'field_c', 
  rename '$.*.field_a' = 'field_c', 
  rename '$.*.*.field_a' = 'field_c'
)  j
from dual;

J                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------
{"field_b":3,"nested":{"nested1":{"field_b":3,"field_c":2}},"field_c":2}


You'll still need to know the maximum level of nesting; you don't need to know the exact paths though

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database