2
0
During this post we will discuss the OUTER Switch in FLATTEN table function. As we know FLATTEN is use to convert semi-structure data to a relational representation. OUTER is the parameter use in FLATTEN having TRUE|FALSE value.
OUTER => TRUE | FALSE
- If
FALSE, any input rows that cannot be expand, either because they cannot be access in the path or because they have zero fields or entries, are completely omit from the output. - If
TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).
In below JSON, we have Customer ARRAY having three records with details Invoice and Accessory. Please note, for third record i.e. “Name”: “Third Employee” we don’t have “Accessory” details. While Accessory details is available for first two employees.
CREATE OR REPLACE TABLE RAW_SOURCE (
JSON_DATA VARIANT
);
insert into raw_source
select
parse_json ('{
"Customer": [
{
"Type": "Primary",
"Name": "First Employee",
"gender": "Male",
"phone": "9560315720",
"Invoice": [
{
"Invc_no": "I-101",
"Invc_amt": "100"
}
],
"Accessory": [
{
"ProdType": "Xerox",
"Prodcolor": "White"
}
]
},
{
"Type": "Primary",
"Name": "Second Employee",
"gender": "Male",
"phone": "880315720",
"Invoice": [
{
"Invc_no": "I-102",
"Invc_amt": "300"
}
],
"Accessory": [
{
"ProdType": "Printer",
"Prodcolor": "Black"
}
]
},
{
"Type": "Primary",
"Name": "Third Employee",
"gender": "Female",
"phone": "123456789",
"Invoice": [
{
"Invc_no": "I-103",
"Invc_amt": "300"
}
],
}
]
}')
So when I try to query the JSON, Output does not flatten the “Third Employee”. We get two rows in output and filter the 3rd record.
select custd.value:Type,
custd.value:Name,
custd.value:gender,
custd.value:phone,
custd.value:Invoice,
inv.value:Invc_no,
inv.value:Invc_amt,
custd.value:Accessory,
access.value:ProdType,
access.value:Prodcolor
from raw_source src,
lateral flatten(input=>src.Json_data:Customer) custd ,
lateral flatten(input=>custd.value:Invoice) inv,
lateral flatten(input=>custd.value:Accessory,OUTER => TRUE) access;
As per the above screenshot, we can see by including OUTER=TRUE, we are getting Accessory record details as well.
Note:
Syntax:
FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view.
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ] [ , OUTER => TRUE | FALSE ] [ , RECURSIVE => TRUE | FALSE ] [ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
