Problem

You parse a JSON string using parse_json and attempt to flatten the ARRAY using the explode function. The following image shows a notebook cell with an example. 

The code returns the following error where $.Company.Department' are the example object and property names.

[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "explode(variant_get(json_data, $.Company.Department))" due to data type mismatch: The first parameter requires the ("ARRAY" or "MAP") type, however "variant_get(json_data, $.Company.Department)" has the type "VARIANT". SQLSTATE: 42K09

Cause

The parse_json function returns a VARIANT type, but the explode function requires an ARRAY type as a parameter. 

Solution

Use the variant_get function (AWS | Azure | GCP) or variant_explode table-valued function (AWS | Azure | GCP) to cast the payload to an ARRAY type. Then use the explode function to flatten it. 

Example using variant_get

Replace the example JSON object with your own object, and adjust $.Company.Department' to your own object and property names. 

with all_data as (
  select 
    variant_get(
      parse_json(
        '{"Company":{"Department":[{"@id":"001","@name":"Sales","Employee":[{"@id":"E001","Contact":{"Email":"johndoe@example.com","Phone":{"#text":"555-1234","@type":"work"}},"Name":"John Doe","Projects":{"Project":  [{"@code":"P001","Budget":{"#text":"100000","@currency":"USD"},"Title":"Project Alpha"},{"@code":"P002","Budget":{"#text":"75000","@currency":"USD"},"Title":"Project Beta"}]},"Role":"Manager"},{"@id":"E002","Contact":{"Email":"janesmith@example.com","Phone":{"#text":"555-9876","@type":"mobile"}},"Name":"Jane Smith","Role":"Sales Representative","Sales":{"Region":{"@name":"North","Achieved":"450000","Target":"500000"}}}]},{"@id":"002","@name":"Engineering","Team":[{"@name":"Development","Employee":[{"@id":"E003","Name":"Alice Johnson","Role":"Software Engineer","Skills":{"Skill":[{"#text":"Python","@level":"advanced"},{"#text":"Java","@level":"intermediate"},{"#text":"Go","@level":"beginner"}]}},{"@id":"E004","Name":"Bob Lee","Role":"DevOps Engineer","Tools":{"Tool":["Docker","Kubernetes"]}}]},{"@name":"QA","Employee":{"@id":"E005","Name":"Charlie Brown","Responsibilities":{"Responsibility":["Automated Testing","Manual Testing"]},"Role":"QA Analyst"}}]}]}}'
      ), 
      '$.Company.Department'
    ) as json_data
) 
select 
  explode(
    CAST(json_data AS ARRAY < VARIANT >)
  ) as dept 
from 
  all_data

The following image shows the example code’s output in a notebook cell.

Example using variant_explode

Replace the example JSON object with your own object, and adjust $.Company.Department' to your own object and property names. 

with all_data as (
  select 
    parse_json(
   '{"Company":{"Department":[{"@id":"001","@name":"Sales","Employee":[{"@id":"E001","Contact":{"Email":"johndoe@example.com","Phone":{"#text":"555-1234","@type":"work"}},"Name":"John Doe","Projects":{"Project":[{"@code":"P001","Budget":{"#text":"100000","@currency":"USD"},"Title":"Project Alpha"},{"@code":"P002","Budget":{"#text":"75000","@currency":"USD"},"Title":"Project Beta"}]},"Role":"Manager"},{"@id":"E002","Contact":{"Email":"janesmith@example.com","Phone":{"#text":"555-9876","@type":"mobile"}},"Name":"Jane Smith","Role":"Sales Representative","Sales":{"Region":{"@name":"North","Achieved":"450000","Target":"500000"}}}]},{"@id":"002","@name":"Engineering","Team":[{"@name":"Development","Employee":[{"@id":"E003","Name":"Alice Johnson","Role":"Software Engineer","Skills":{"Skill":[{"#text":"Python","@level":"advanced"},{"#text":"Java","@level":"intermediate"},{"#text":"Go","@level":"beginner"}]}},{"@id":"E004","Name":"Bob Lee","Role":"DevOps Engineer","Tools":{"Tool":["Docker","Kubernetes"]}}]},{"@name":"QA","Employee":{"@id":"E005","Name":"Charlie Brown","Responsibilities":{"Responsibility":["Automated Testing","Manual Testing"]},"Role":"QA Analyst"}}]}]}}'
    ) as json_data
) 
select 
  t.value 
from 
  all_data 
  join lateral variant_explode(
    variant_get(
      json_data, '$.Company.Department'
    )
  ) as t

The following image shows the example code’s output in a notebook cell.