JSON_OBJECT_FLATTEN

Description

JSON_OBJECT_FLATTEN flattens a nested JSON object into a single-level JSON object whose keys are the dot-joined paths to each leaf value. It follows the NiFi FlattenJson "keep-arrays" semantics: only objects are recursively walked, while arrays, scalars, nulls, and empty objects are kept as opaque leaf values.

If the top-level input is not an object (for example, a scalar, an array, or null), it is returned unchanged.

Syntax

JSON_OBJECT_FLATTEN(<json_value>)

Parameters

<json_value> - The JSON value to flatten. Must be of JSON type.

Return Value

Returns a JSON value:

• If the input is a nested JSON object, returns a single-level JSON object whose keys are the dot-joined paths to each leaf value.
• If the input is not an object (scalar, array, or null), returns the input unchanged.
• If the input is NULL, returns NULL.

Examples

Basic nested object flattening

SELECT json_object_flatten('{"a":{"b":2}}');

+--------------------------------------+
| json_object_flatten('{"a":{"b":2}}') |
+--------------------------------------+
| {"a.b":2}                            |
+--------------------------------------+

Deeply nested object

SELECT json_object_flatten('{"a":{"b":{"c":3}}}');

+--------------------------------------------+
| json_object_flatten('{"a":{"b":{"c":3}}}') |
+--------------------------------------------+
| {"a.b.c":3}                                |
+--------------------------------------------+

Already-flat object

SELECT json_object_flatten('{"a":1,"b":"hi"}');

+-----------------------------------------+
| json_object_flatten('{"a":1,"b":"hi"}') |
+-----------------------------------------+
| {"a":1,"b":"hi"}                        |
+-----------------------------------------+

Arrays preserved as opaque leaves

SELECT json_object_flatten('{"a":[{"b":1},{"b":2}]}');

+------------------------------------------------+
| json_object_flatten('{"a":[{"b":1},{"b":2}]}') |
+------------------------------------------------+
| {"a":[{"b":1},{"b":2}]}                        |
+------------------------------------------------+

SELECT json_object_flatten('{"a":{"b":[1,2,3]}}');

+--------------------------------------------+
| json_object_flatten('{"a":{"b":[1,2,3]}}') |
+--------------------------------------------+
| {"a.b":[1,2,3]}                            |
+--------------------------------------------+

Top-level non-object values pass through unchanged

SELECT json_object_flatten('42');

+---------------------------+
| json_object_flatten('42') |
+---------------------------+
| 42                        |
+---------------------------+

SELECT json_object_flatten('[1,2,{"x":3}]');

+--------------------------------------+
| json_object_flatten('[1,2,{"x":3}]') |
+--------------------------------------+
| [1,2,{"x":3}]                        |
+--------------------------------------+

NULL input

SELECT json_object_flatten(NULL);

+---------------------------+
| json_object_flatten(NULL) |
+---------------------------+
| NULL                      |
+---------------------------+

Mixed nested object with scalars, arrays, and sub-objects

SELECT json_object_flatten('{"x":{"s":1,"a":[1,2],"o":{"k":"v"}}}');

+--------------------------------------------------------------+
| json_object_flatten('{"x":{"s":1,"a":[1,2],"o":{"k":"v"}}}') |
+--------------------------------------------------------------+
| {"x.s":1,"x.a":[1,2],"x.o.k":"v"}                            |
+--------------------------------------------------------------+