Preserve Key Order: Loading JSON data into Snowflake VARIANT column using Python script

Question

I was trying to extract the JSON response data from an API and load it into Snowflake VARIANT column using Python Script.

While loading the data, I noticed that the keys are re-arranged in alphabetical order.

Python/Postman data:

{
  "Data": [
    {
      "CompanyID": 3522,
      "MarketID": 23259,
      "MarketName": "XYZ_Market"
      "LocationID": 17745,
      "LocationName": "XYZ_Location"
    }
}

Snowflake data:

{
  "Data": [
    {
      "CompanyID": 3522,
      "LocationID": 17745,
      "LocationName": "XYZ_Location",
      "MarketID": 23259,
      "MarketName": "XYZ_Market"
    }
}

I was using PARSE_JSON() query function to load the data into snowflake. Is there any way to preserve the order of keys ?

What version of Python are you using? Please also provide your Python code and snowflake schema — sytech
JSON is an un-ordered collection of name and value pair. You cannot guarantee order in JSON — Rajib Deb
What is the reason that the order of the keys is necessary? Just curious, as I've seen this question before, but never understood it. If you plan to query the data in Snowflake, the order of the keys will not matter. — Mike Walton
its not python , it is parse_json function bringing key in order to increases the performance. — PIG
This was a question raised by one of our client and he wanted to know why exactly the JSON data in variant column in snowflake can't preserve the key order the same way Postman does. So it seems PARSE_JSON() snowflake function is changing the order for better performance. I know that we always have an option to flatten the data and represent the data/attributes in any order we would like to see using a curated view but I would like to know other options that we have from experts. — Saroj Somasundaram

sytech sytech · Accepted Answer · 2020-10-30T01:45:56

In Python 3.6+ dictionaries maintain their insertion order. However, as noted in the snowflake docs JSON objects are unordered. So, you may be limited by how your data is stored.

If you need to maintain order, consider an array of arrays, instead.

    [
      ["CompanyID", 3522],
      ["MarketID", 23259],
      ["MarketName", "XYZ_Market"],
      ["LocationID", 17745],
      ["LocationName", "XYZ_Location"]
    ]

Preserve Key Order: Loading JSON data into Snowflake VARIANT column using Python script

1 Answers