Forum Discussion

fabriciocarboni's avatar
fabriciocarboni
New Contributor II
2 years ago

Mapper strange behaviour when using jsonpath to get array and sink to Snowflake

I have been having a nightmare with mapper 😄

The pipeline:

 

 

I have this json input:

{
"plantId": 1000349,
"plantName": "some name",
"plantStatusDesc": "Operational",
"releaseDate": "2024-06-04T21:15:20Z[UTC]",
"industryCode": "09",
"industryCodeDesc": "Pulp, Paper & Wood",
"primarySicId": "2631",
"primarySicDesc": "Paperboard Mills",
"physicalAddress": {
"city": "some city",
"stateName": "some state",
"countryName": "some country"
},
"owner": [
{
"companyName": "Georgia-Pacific LLC"
}
]
}

 The problem is with owner array.

In mapper I have like this:

In the mapper preview it seems ok:

However, when inserted in Snowflake, It inserts like this:

And the correct way should be like this: This was achieved because I load the historical data via a python script I developed.

This column in Snowflake is defined as VARIANT data type. Am I missing something in the mapper?

 

 

 

 

1 Reply

  • fabriciocarboni's avatar
    fabriciocarboni
    New Contributor II

    Im wondering why snaplogic snap mapper is generating a downstream output like this

    "[{companyName=Georgia-Pacific LLC}]"

     I got his error in the mapper error output when I tried to change the snowflake data type from VARIANT  to OBJECT and I've got this error.