Parse JSON data using case insensitive column names in Snowflake

user_4991743 0
Viewed 318

I am trying to parse the Json document and create a view. When parsing JSON can we use case insensitive column name. Is there any way?

SELECT 
   parse_json(column1) AS json_data
FROM VALUES
   ('{
      "ID": 1,
      "color": "black",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgb": "255,255,255",
        "hex": "#000"
      }
    }') as raw_json;

This would run and pull data, select json_data:ID::integer as ID from colors

This wouldn't pull data. Is there any way to make below also work? select json_data:id::integer as ID from colors

snowflake-cloud-data-platform sql json javascript
1 Answers

You can use GET_IGNORE_CASE as:

SELECT  GET_IGNORE_CASE(to_object(json_data),'id')::integer  as ID1,
        GET_IGNORE_CASE(to_object(json_data),'ID')::integer  as ID2 
FROM
(
  SELECT 
     parse_json(column1) AS json_data
  FROM VALUES
     ('{
        "ID": 1,
        "color": "black",
        "category": "hue",
        "type": "primary",
        "code": {
          "rgb": "255,255,255",
          "hex": "#000"
        }
      }') as raw_json
  )
user_146244090
Related