Handle partial Stringified Partial Key-value JSON VARIANT on SQL

user_6821505 0
Viewed 65

I have a variant object like the below:

  {
  "build_num": "111",
  "city_name": "Paris",    
  "rawData": "\"sku\":\"AAA\",\"price\":19.98,\"currency\":\"USD\",\"quantity\":1,\"size\":\"\"}",
  "country": "France"
}

So you can see that parts of it are regular key-value pairs like build num and city name, but then we have raw data which its value is a stringified version of a JSON

I would like to create a variant from this that will look like:

       {
          "build_num": "111",
          "city_name": "Paris",    
          "rawData": {
    
              "sku":"AAA",
              "price":19.98,
              "currency":"USD",
               "quantity":1}

         "country": "France"
    }

AND I would like to do this all in SQL (Snowflake) - is that possible?

snowflake-cloud-data-platform sql json
2 Answers

so to poke the "data" into I have used a CTE, and escaped the sub json, so it is in the DB as you describe. I also had to add the missing start of object token {, to make rawData valid.

WITH data AS (
    SELECT parse_json(json) as json
    FROM VALUES 
        ('{"build_num": "111","city_name": "Paris","country": "France","rawData": "{\\"sku\\":\\"AAA\\",\\"price\\":19.98,\\"currency\\":\\"USD\\",\\"quantity\\":1,\\"size\\":\\"\\"}"}')
    v( json)
)
SELECT 
    json,
    json:rawData as raw_data,
    parse_json(raw_data) as sub_json,
    OBJECT_INSERT(json, 'rawData', sub_json, true) as all_json
FROM data;

so this show step by step transforming the data, parsing it via PARSE_JSON, and re inject via OBJECT_INSERT the result into the original object.

WITH data AS (
    SELECT parse_json(json) as json
    FROM VALUES 
        ('{"build_num": "111","city_name": "Paris","country": "France","rawData": "{\\"sku\\":\\"AAA\\",\\"price\\":19.98,\\"currency\\":\\"USD\\",\\"quantity\\":1,\\"size\\":\\"\\"}"}')
    v( json)
)
SELECT 
    OBJECT_INSERT(json, 'rawData', parse_json(json:rawData), true) as all_json
FROM data;
user_439920

TRY_PARSE_JSON will clear off additional slashes from JSON element and below is reference:

 select
            column1 as n,
            try_parse_json(column1) as v 
                   from
        values
            (
                '{
  "build_num": "111",
  "city_name": "Paris",    
  "rawData": "{\"sku\":\"AAA\",\"price\":19.98,\"currency\":\"USD\",\"quantity\":1,\"size\":\"\"}",
  "country": "France"}'
            ) as vals;
user_144426640
Related