JSON Value

• Peter Kruis

  Mr or Mrs. 500

  Points: 578

  More actions

  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

• ratbak

  Default port

  Points: 1411

  More actions

  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?

• Peter Kruis

  Mr or Mrs. 500

  Points: 578

  More actions

  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.