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}
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