Post reply

JSON Value

  • September 28, 2020 at 11:35 am

    Go to Answer

    #3791267

    I'm trying to get the Json value from the lat and lon, however keep getting NULL as a result.

    What is the right way to get the Lat and Lon values?

    DECLARE @JsonValue VARCHAR(MAX) = '{ "CoordSets": [ [ { "Lon": "4.6219456", "Lat": "51.9642514" } ] ] }';

    SELECT
       JSON_QUERY(@JsonValue, '$.CoordSets[0]'),--returns a value, but all
       JSON_VALUE(@JsonValue, '$.CoordSets[0]'),--returns null
       JSON_QUERY(@JsonValue, '$.CoordSets[0].Lat'),--returns null
       JSON_VALUE(@JsonValue, '$.CoordSets[0].Lat');--returns null

  • September 28, 2020 at 1:35 pm

    Answer

    #3791306

    You have a nested array.

    SELECT JSON_VALUE(@JsonValue, '$.CoordSets[0][0].Lat');--returns 51.9642514

    Are the double square brackets intentional?

  • September 28, 2020 at 1:38 pm

    #3791307

    Thanks a lot! That indeed did the job!

    Not sure, getting this results from a supplier, which I think get this data from MapQuest.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply