CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platformflatten
Deep
Deep

Reputation: 11

Flatten variable length JSON in Snowflake

I have a JSON in which certain attributes are null for some records and some records have all the key value pair. When I try to flatten the JSON in snowflake using lateral flatten, I am losing the records where certain attributes are null or not present in the JSON record. Below is a sample example of the JSON I have.

1) {
"course": 
  {
    "className": "CAPSTONE RESEARCH PROJECT",
    "courseType": "LECTURE",
    "crn": 11223,
    "crossListedCourses": [
      {
        "className": "CAPSTONE RESEARCH PROJECT",
        "courseType": "LECTURE",
        "crn": 11230,
        "lmsCourseID": "202110_FALL_SOC436_11230",
        "partialTermCode": 1,
        "subject": "SOC",
        "subjectCode": "436",
        "term": "202110"
      }
  ]
  }
}'


2)'{
"course": 
  {
    "className": "LALA LAND",
    "courseType": "LECTURE",
    "crn": 11224,
    "crossListedCourses": [
      {
        "className": "LALA LAND",
        "courseType": "LECTURE",
        "crn": 11231,
        "lmsCourseID": "202110_FALL_SOC436_11231",
        "partialTermCode": 2,
        "subject": "SOC",
        "subjectCode": "437",
        "term": "202110"
      }
  ]
  }
}'

3) '{
"course": 
  {
    "className": "ZOMBIE LAND",
    "courseType": "LECTURE",
    "crn": 11225
    
  }
}'

When doing lateral flatten into a new table, I am losing the 3rd record using the below query

    SELECT 
    json_string:course.className,
    json_string:course.courseType,
    json_string:course.crn,
    json_string:crossListedCourses,
    prod.value:className,
    prod.value:courseType,
    prod.value:crn,
    prod.value:lmsCourseID,
    prod.value:partialTermCode,
    prod.value:subject,
    prod.value:subjectCode,
    prod.value:term
    FROM variant_example, (LATERAL FLATTEN (input => json_string:course.crossListedCourses)) as 
    prod

Upvotes: 1

Views: 188

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

FLATTEN - OUTER:

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

Default: FALSE

FROM variant_example
,(LATERAL FLATTEN (input => json_string:course.crossListedCourses, OUTER => TRUE))..

Upvotes: 1

Related Questions