FLATTEN

FLATTEN is a Set Returning Function.

It returns a JSONB object with the fields "index", "key" and "value" for each element contained in the input JSONB.

FLATTEN(jsonb_expression)

jsonb_expression

The JSONB value to flatten.

FLATTEN behavior

Objects

If the input JSONB is an object, FLATTEN returns a JSONB for each key-value pair in the object. Each JSONB has

• index: null
• key: the key of the key-value pair
• value: the value of the key-value pair

SELECT * FROM FLATTEN('{"name":"Barnie","type":"Dog","big":true}');

                   flatten
----------------------------------------------
 {"index":null,"key":"big","value":true}
 {"index":null,"key":"name","value":"Barnie"}
 {"index":null,"key":"type","value":"Dog"}
(3 rows)

Arrays

If the input is an array, FLATTEN returns a JSONB for each element of the array. Each JSONB has

• index: the index of the element
• key: null
• value: the value of the element

SELECT * FROM FLATTEN('[7,[],1]');

              flatten
-----------------------------------
 {"index":0,"key":null,"value":7}
 {"index":1,"key":null,"value":[]}
 {"index":2,"key":null,"value":1}
(3 rows)

Primitives

If the input is a primitive type (string, number, boolean, or null), FLATTEN returns a single JSONB that has

• index: null
• key: null
• value: the primitive itself

SELECT * FROM FLATTEN('3');
--  {"index":null,"key":null,"value":3}

Syntactic Sugar

There is an alternate syntax for FLATTEN that accepts additional parameters in the form <jsonpath> AS <column name>.

This applies <jsonpath> to each row that would have been returned by the single argument version of FLATTEN, and returns the value obtained after applying the jsonpath as <column name>.

SELECT * FROM FLATTEN('[7,[],1]', $.index AS i, $.value AS v) AS f;

 i | v
---+----
 0 | 7
 1 | []
 2 | 1
(3 rows)

The AS <alias> part is mandatory when using the multi argument syntax for FLATTEN.

JSON_OBJECT

JSON_OBJECT is an alias for FLATTEN.