Flatten JSON Data on snowflake

user_4155334 0
Viewed 293

below is the Json data I'm trying to Flatten on snowflake

Json Document :

[
"empDetails": [
    {
        "kind": "person",
        "fullName": "John Doe",
        "age": 22,
        "gender": "Male",
        "phoneNumber": {
            "areaCode": "206",
            "number": "1234567"
        },
        "children": [
            {
                "name": "Jane",
                "gender": "Female",
                "age": "6"
            },
            {
                "name": "John",
                "gender": "Male",
                "age": "15"
            }
        ],
        "citiesLived": [
            {
                "place": "Seattle",
                "yearsLived": [
                    "1995"
                ]
            },
            {
                "place": "Stockholm",
                "yearsLived": [
                    "2005"
                ]
            }
        ]
    },
    {
        "kind": "person",
        "fullName": "Mike Jones",
        "age": 35,
        "gender": "Male",
        "phoneNumber": {
            "areaCode": "622",
            "number": "1567845"
        },
        "children": [
            {
                "name": "Earl",
                "gender": "Male",
                "age": "10"
            },
            {
                "name": "Sam",
                "gender": "Male",
                "age": "6"
            },
            {
                "name": "Kit",
                "gender": "Male",
                "age": "8"
            }
        ],
        "citiesLived": [
            {
                "place": "Los Angeles",
                "yearsLived": [
                    "1989",
                    "1993",
                    "1998",
                    "2002"
                ]
            },
            {
                "place": "Washington DC",
                "yearsLived": [
                    "1990",
                    "1993",
                    "1998",
                    "2008"
                ]
            },
            {
                "place": "Portland",
                "yearsLived": [
                    "1993",
                    "1998",
                    "2003",
                    "2005"
                ]
            },
            {
                "place": "Austin",
                "yearsLived": [
                    "1973",
                    "1998",
                    "2001",
                    "2005"
                ]
            }
        ]
    },
    {
        "kind": "person",
        "fullName": "Anna Karenina",
        "age": 45,
        "gender": "Female",
        "phoneNumber": {
            "areaCode": "425",
            "number": "1984783"
        },
        "citiesLived": [
            {
                "place": "Stockholm",
                "yearsLived": [
                    "1992",
                    "1998",
                    "2000",
                    "2010"
                ]
            },
            {
                "place": "Russia",
                "yearsLived": [
                    "1998",
                    "2001",
                    ""
                ]
            },
            {
                "place": "Austin",
                "yearsLived": [
                    "1995",
                    "1999"
                ]
            }
        ]
    }
]

}

In this data I have 3 employees and their details like Name, children, cities Lived but for one of the employee "Anna Karenina" children details are not there, but for other 2 employees have children data.

because of the missing children details I'm not able to flatten 3rd emp data.

below is what I have tried so far :

Snowflake Flatten Json Code :

select empd.value:kind,
empd.value:fullName,
empd.value:age,
empd.value:gender,   
--empd.value:phoneNumber,
empd.value:phoneNumber.areaCode, 
empd.value:phoneNumber.number ,
empd.value:children -- flattening childrean 
//chldrn.value:name,
//chldrn.value:gender,
//chldrn.value:age,
//city.value:place,
//yr.value:yearsLived
from my_json emp , lateral flatten(input=>emp.Json_data:empDetails) empd , 
lateral flatten(input=>empd.value:children) chldrn,
//lateral flatten(input=>empd.value:citiesLived) city,
//lateral flatten(input=>city.value:yearsLived) yr
snowflake-cloud-data-platform
1 Answers

You need to use OUTER switch:

FLATTEN

OUTER => TRUE | FALSE

  • If FALSE, any input rows that cannot be expanded, either because they cannot be accessed in the path or because they have zero fields or entries, are completely omitted from the output.

  • If TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).

select empd.value:kind,
  empd.value:fullName,
  empd.value:age,
  empd.value:gender,   
  empd.value:phoneNumber,
  empd.value:phoneNumber.areaCode, 
  empd.value:phoneNumber.number ,
  empd.value:children, 
  chldrn.value:name,
  chldrn.value:gender,
  chldrn.value:age,
  city.value:place,
  yr.value:yearsLived
from my_json emp,
  lateral flatten(input=>emp.Json_data:empDetails) empd , 
  lateral flatten(input=>empd.value:children, OUTER => TRUE) chldrn,   -- <HERE>
  lateral flatten(input=>empd.value:citiesLived) city,
  lateral flatten(input=>city.value:yearsLived) yr
user_50708790
Related