Snowflake Error parsing JSON: unfinished string, pos <number>-CodePudding

I am trying to query a varchar column called "JsonCode" from the "Weather" table in snowflake. The "JsonCode" column looks like this:

{
  "Date": "2019-11-07T12:28:18",
  "CurrentTemp": "47Â°F",
  "WeatherIconStatus": "clear-day",
  "LowTemp": "21Â°F",
  "HighTemp": "50Â°F",
  "WindSpeed": "6 mph",
  "TempCategory": "Hot",
  "ForecastData": [
    {
      "Date": "2019-11-08T00:00:00",
      "WeatherIconStatus": "clear-day",
      "LowTemp": "26Â°F",
      "HighTemp": "51Â°F",
      "WindSpeed": "3 mph"
    },
    {
      "Date": "2019-11-09T00:00:00",
      "WeatherIconStatus": "clear-day",
      "LowTemp": "28Â°F",
      "HighTemp": "56Â°F",
      "WindSpeed": "7 mph"
    }
  ],
  "PrecipitationReportData": {
    "ReportDateTimeAsDate": "2019-11-07T04:45:14",
    "PrecipitationConditions": "",
    "ForecastText": "Clear",
    "NewPrecipitationReadings": {
      "Overnight": {
        "PrecipitationReading": "0in"
      },
      "TwentyFourHours": {
        "PrecipitationReading": "0in"
      },
      "FortyEightHours": {
        "PrecipitationReading": "0in"
      },
      "SevenDays": {
        "PrecipitationReading": "0in"
      }
    },
    "AreaReadings": {
      "City": {
        "PrecipitationReading": "0in"
      },
      "Town": {
        "PrecipitationReading": "0in"
      },
      "Highway": {
        "PrecipitationReading": null
      }
    },
    "SeasonPrecipitation": {
      "PrecipitationReading": "44in"
    }
  },
  "Links": [
    {
      "Href": "https://weather.com",
      "Rel": "self",
      "Method": "GET"
    }
  ]
}

I am specifically trying to grab the "WindSpeed" data from 2019-11-07. I created a query that looks like this.

SELECT value:WindSpeed::varchar FROM "DATABASE"."DBO"."WEATHER" 
    , lateral flatten(input => PARSE_JSON(JsonCode):windspeed);

The response I received is

Error parsing JSON: unfinished string, pos <number>

I have been unable to find any documentation around this error. Any help is appreciated. Note: I cut out some of the Json since there is multiple days of forecasted data.

CodePudding user response：

This should work without the need of a lateral flatten:

select parse_json(j):WindSpeed
from data

With the posted sample JSON:

with data as (
select $${
  "Date": "2019-11-07T12:28:18",
  "CurrentTemp": "47Â°F",
  "WeatherIconStatus": "clear-day",
  "LowTemp": "21Â°F",
  "HighTemp": "50Â°F",
  "WindSpeed": "6 mph",
  "TempCategory": "Hot",
  "ForecastData": [
    {
      "Date": "2019-11-08T00:00:00",
      "WeatherIconStatus": "clear-day",
      "LowTemp": "26Â°F",
      "HighTemp": "51Â°F",
      "WindSpeed": "3 mph"
    },
    {
      "Date": "2019-11-09T00:00:00",
      "WeatherIconStatus": "clear-day",
      "LowTemp": "28Â°F",
      "HighTemp": "56Â°F",
      "WindSpeed": "7 mph"
    }
  ],
  "PrecipitationReportData": {
    "ReportDateTimeAsDate": "2019-11-07T04:45:14",
    "PrecipitationConditions": "",
    "ForecastText": "Clear",
    "NewPrecipitationReadings": {
      "Overnight": {
        "PrecipitationReading": "0in"
      },
      "TwentyFourHours": {
        "PrecipitationReading": "0in"
      },
      "FortyEightHours": {
        "PrecipitationReading": "0in"
      },
      "SevenDays": {
        "PrecipitationReading": "0in"
      }
    },
    "AreaReadings": {
      "City": {
        "PrecipitationReading": "0in"
      },
      "Town": {
        "PrecipitationReading": "0in"
      },
      "Highway": {
        "PrecipitationReading": null
      }
    },
    "SeasonPrecipitation": {
      "PrecipitationReading": "44in"
    }
  },
  "Links": [
    {
      "Href": "https://weather.com",
      "Rel": "self",
      "Method": "GET"
    }
  ]
}$$ j
)

select parse_json(j):WindSpeed
from data