Use of Lateral Flatten for JSON in Snowflake-CodePudding

I have the following JSON as a variant field in snowflake. I get NULLS when querying the data as shown below - -

create or replace table car_sales
( 
  src variant
)
as
select parse_json(column1) as src
from values
('{
  "MyData": {
    "DataID": "16784",
    "EmpFirstName": "Jenny",
    "EmpLastName": "Test1",
    "Description": "My Company Data",
    "Assignment": [
      {
        "AssignentId": "1077",
        "AssignentName": "Multi Location",
        "AssignentDescription": "usa",
      },
    ],
    "salary": 21557
  },
  "Update": true
}') v;

When I query the data I get Nulls -

select value:AssignentId::string as "1ProductName"
from car_sales,
lateral flatten(input => src:Assignment);

Output is NULL

Can you help to troubleshoot why this is happening?

CodePudding user response：

Try adding the MyData qualifier in the lateral flatten:

with car_sales(src) as (
    select
        parse_json(column1) as src
    from
    values
        (
            '{ "MyData": {
     "DataID": "16784",
    "EmpFirstName": "Jenny",
    "EmpLastName": "Test1",
    "Description": "My Company Data",
    "Assignment": [
      {
        "AssignentId": "1077",
        "AssignentName": "Multi Location",
        "AssignentDescription": "usa",
      },
    ],
    "salary": 21557
    }, "Update": true }'
        )
)
select
    value:AssignentId::string as "1ProductName"
from
    car_sales,
    lateral flatten(input => src:MyData:Assignment);

1ProductName
1077

CodePudding user response：

You are trying to access the child node directly. Specify the parent node as well

select value:AssignentId::string as "1ProductName"
from car_sales,
lateral flatten(input => src:MyData:Assignment);

input => src:Assignment

should be

input => src:MyData:Assignment