Product Documentation

Appearance

All platforms

FLATTEN ​

Prerequisite: This function is in beta mode. To use it, enable the enable_full_json, enable_full_funcscan, and enable_full_lateral_join configuration parameters. For more details, refer to JSON Functions.

The FLATTEN function expands JSONB values into multiple rows, which is useful for extracting structured data from JSON documents. It can also be referenced using the alias JSON_TABLE.

sql
FLATTEN (
    jsonb_expression, 
    [jsonpath AS jsonpath_alias, jsonpath2 AS jsonpath_alias2, ...]
) AS flatten_alias
jsonb_expression
Any expression that evaluates to JSONB, including a column reference.
jsonpath
Any valid JSON path expressions used to extract new columns that can be referenced in other clauses.
jsonpath_alias
Alias name for the extracted column corresponding to the JSON path expression.
flatten_alias
Alias name for the resulting flattened table.

Output ​

The FLATTEN function returns rows, each containing a JSONB object with a fixed set of fields:

sql
{"index": <index>, "key": <key>, "value": <value>}
index
For JSON objects and arrays, this field contains the index of the flattened value; otherwise, it is 0.
key
For JSON objects, this field contains the key associated with the flattened value; otherwise NULL.
value
The flattened value of the element from the JSON array/object.

Examples: Basic Usage ​

Flatten a JSON object ​

sql
select * from flatten('{"INTC": 49.38, "BRK.A": 550500}');
sql
flatten                  
------------------------------------------
 {"index":0,"key":"BRK.A","value":550500}
 {"index":1,"key":"INTC","value":49.38}
(2 rows)

Flatten a JSON array ​

sql
select * from flatten('{"ticker": ["INTC","BRK.A"]}':$.ticker);
sql
flatten
-------------------------------------
 {"index":0,"key":0,"value":"INTC"}
 {"index":1,"key":1,"value":"BRK.A"}
(2 rows)

Flatten with specified columns ​

sql
select * from flatten('[{"name":"INTC","high":49.38}, {"name":"BRK.A","high":550500}]', 
                               value.name as name, 
                               value.high as high) as F;
sql
name   |  high  
---------+--------
 "INTC"  | 49.38
 "BRK.A" | 550500
(2 rows)

Flatten with JSON_TABLE alias ​

sql
select * from json_table('{"INTC": 49.38, "BRK.A": 550500}');
sql
flatten                  
------------------------------------------
 {"index":0,"key":"BRK.A","value":550500}
 {"index":1,"key":"INTC","value":49.38}
(2 rows)

Examples: Flatten Column ​

For following examples, we'll use this data setup:

sql
create table tickertable(date date, data jsonb);
insert into tickertable values 
    ('2024-01-02', '[{"name":"INTC","high":49.38}, {"name":"BRK.A","high":550500}]'),
    ('2024-01-03', '[{"name":"INTC","high":47.81}, {"name":"BRK.A","high":557900}]'),
    ('2024-01-04', '[{"name":"INTC","high":47.16}, {"name":"BRK.A","high":560400}]');

Lateral flatten a table with a JSONB column ​

sql
select * from tickertable as t, lateral flatten(t.data) as f order by date;
sql
date    |                             data                              |                             f                              
------------+---------------------------------------------------------------+------------------------------------------------------------
 2024-01-02 | [{"high":49.38,"name":"INTC"},{"high":550500,"name":"BRK.A"}] | {"index":0,"key":0,"value":{"high":49.38,"name":"INTC"}}
 2024-01-02 | [{"high":49.38,"name":"INTC"},{"high":550500,"name":"BRK.A"}] | {"index":1,"key":1,"value":{"high":550500,"name":"BRK.A"}}
 2024-01-03 | [{"high":47.81,"name":"INTC"},{"high":557900,"name":"BRK.A"}] | {"index":0,"key":0,"value":{"high":47.81,"name":"INTC"}}
 2024-01-03 | [{"high":47.81,"name":"INTC"},{"high":557900,"name":"BRK.A"}] | {"index":1,"key":1,"value":{"high":557900,"name":"BRK.A"}}
 2024-01-04 | [{"high":47.16,"name":"INTC"},{"high":560400,"name":"BRK.A"}] | {"index":0,"key":0,"value":{"high":47.16,"name":"INTC"}}
 2024-01-04 | [{"high":47.16,"name":"INTC"},{"high":560400,"name":"BRK.A"}] | {"index":1,"key":1,"value":{"high":560400,"name":"BRK.A"}}
(6 rows)

Select the specified columns only ​

sql
select date, name, high from tickertable as t, lateral flatten(t.data, value.name as name, value.high as high) as f order by date;
sql
date    |  name   |  high  
------------+---------+--------
 2024-01-02 | "INTC"  | 49.38
 2024-01-02 | "BRK.A" | 550500
 2024-01-03 | "INTC"  | 47.81
 2024-01-03 | "BRK.A" | 557900
 2024-01-04 | "INTC"  | 47.16
 2024-01-04 | "BRK.A" | 560400
(6 rows)

Use the flattened column in a WHERE clause ​

sql
select date, name, high from tickertable as t, lateral flatten(t.data, value.name as name, value.high as high) as f 
         where high::float > 100 order by date;
sql
date    |  name   |  high  
------------+---------+--------
 2024-01-02 | "BRK.A" | 550500
 2024-01-03 | "BRK.A" | 557900
 2024-01-04 | "BRK.A" | 560400
(3 rows)

Examples: Nested Flatten ​

For following examples, we'll use this data setup:

sql
create table tickertable(date date, data jsonb);
insert into tickertable values 
    ('2024-01-02', '[{"name":"INTC","prices":{"open":49.20,"high":49.38,"low":47.45}}, {"name":"BRK.A","prices":{"open":539496,"high":550500,"low":539496}}]'),
    ('2024-01-03', '[{"name":"INTC","prices":{"open":47.10,"high":47.81,"low":46.80}}, {"name":"BRK.A","prices":{"open":549272,"high":557900,"low":547106}}]'),
    ('2024-01-04', '[{"name":"INTC","prices":{"open":45.72,"high":47.16,"low":45.24}}, {"name":"BRK.A","prices":{"open":557707,"high":560400,"low":551100}}]');

Use flatten to parse nested objects ​

sql
select date, f from tickertable as t, lateral flatten(t.data) as f order by date;
sql
date    |                                                f                                                 
------------+--------------------------------------------------------------------------------------------------
 2024-01-02 | {"index":0,"key":0,"value":{"name":"INTC","prices":{"high":49.38,"low":47.45,"open":49.20}}}
 2024-01-02 | {"index":1,"key":1,"value":{"name":"BRK.A","prices":{"high":550500,"low":539496,"open":539496}}}
 2024-01-03 | {"index":0,"key":0,"value":{"name":"INTC","prices":{"high":47.81,"low":46.80,"open":47.10}}}
 2024-01-03 | {"index":1,"key":1,"value":{"name":"BRK.A","prices":{"high":557900,"low":547106,"open":549272}}}
 2024-01-04 | {"index":0,"key":0,"value":{"name":"INTC","prices":{"high":47.16,"low":45.24,"open":45.72}}}
 2024-01-04 | {"index":1,"key":1,"value":{"name":"BRK.A","prices":{"high":560400,"low":551100,"open":557707}}}
(6 rows)
sql
select date, ticker, type, price 
         from tickertable as t, 
         lateral flatten(t.data, value.name as ticker, value as value) as f, 
         lateral flatten(f.value:prices, key as type, value as price) as f2 
         order by date;
sql
date    | ticker  |  type  | price  
------------+---------+--------+--------
 2024-01-02 | "INTC"  | "high" | 49.38
 2024-01-02 | "INTC"  | "low"  | 47.45
 2024-01-02 | "INTC"  | "open" | 49.20
 2024-01-02 | "BRK.A" | "high" | 550500
 2024-01-02 | "BRK.A" | "low"  | 539496
 2024-01-02 | "BRK.A" | "open" | 539496
 2024-01-03 | "INTC"  | "high" | 47.81
 2024-01-03 | "INTC"  | "low"  | 46.80
 2024-01-03 | "INTC"  | "open" | 47.10
 2024-01-03 | "BRK.A" | "high" | 557900
 2024-01-03 | "BRK.A" | "low"  | 547106
 2024-01-03 | "BRK.A" | "open" | 549272
 2024-01-04 | "INTC"  | "high" | 47.16
 2024-01-04 | "INTC"  | "low"  | 45.24
 2024-01-04 | "INTC"  | "open" | 45.72
 2024-01-04 | "BRK.A" | "high" | 560400
 2024-01-04 | "BRK.A" | "low"  | 551100
 2024-01-04 | "BRK.A" | "open" | 557707
(18 rows)