SQL Server JSON retrieval based on specific selection criteria

Question

Using SQL Server JSON functionality how is it possible to extract just the postcode from the following google maps JSON output?

Please Note: Unfortunately the address_components are not always returned in the same order so looking for

address_components[0]

will not work in this case. I have had them returned from the Google API in various different places so a way of searching for 'postal_code' is required.

{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "BH21 7JH",
               "short_name" : "BH21 7JH",
               "types" : [ "postal_code" ]
            },
            {
               "long_name" : "Horton",
               "short_name" : "Horton",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Wimborne",
               "short_name" : "Wimborne",
               "types" : [ "postal_town" ]
            },
            {
               "long_name" : "Dorset",
               "short_name" : "Dorset",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "England",
               "short_name" : "England",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United Kingdom",
               "short_name" : "GB",
               "types" : [ "country", "political" ]
            }
         ],
         "formatted_address" : "Horton, Wimborne BH21 7JH, UK",
         "geometry" : {
            "bounds" : {
               "northeast" : {
                  "lat" : 50.868818,
                  "lng" : -1.9346172
               },
               "southwest" : {
                  "lat" : 50.85362689999999,
                  "lng" : -1.9478702
               }
            },
            "location" : {
               "lat" : 50.8638781,
               "lng" : -1.9399482
            },
            "location_type" : "APPROXIMATE",
            "viewport" : {
               "northeast" : {
                  "lat" : 50.868818,
                  "lng" : -1.9346172
               },
               "southwest" : {
                  "lat" : 50.85362689999999,
                  "lng" : -1.9478702
               }
            }
         },
         "place_id" : "ChIJC21uTfK8c0gRVWfG433_kxM",
         "types" : [ "postal_code" ]
      }
   ],
   "status" : "OK"
}

I have got this far:

DECLARE @jsondata nvarchar(max);
SET @jsondata ='{"results":[{"address_components":[{"long_name":"BH21 7JH","short_name":"BH21 7JH","types":["postal_code"]},{"long_name":"Horton","short_name":"Horton","types":["locality","political"]},{"long_name":"Wimborne","short_name":"Wimborne","types":["postal_town"]},{"long_name":"Dorset","short_name":"Dorset","types":["administrative_area_level_2","political"]},{"long_name":"England","short_name":"England","types":["administrative_area_level_1","political"]},{"long_name":"United Kingdom","short_name":"GB","types":["country","political"]}],"formatted_address":"Horton, Wimborne BH21 7JH, UK","geometry":{"bounds":{"northeast":{"lat":50.868818,"lng":-1.9346172},"southwest":{"lat":50.85362689999999,"lng":-1.9478702}},"location":{"lat":50.8638781,"lng":-1.9399482},"location_type":"APPROXIMATE","viewport":{"northeast":{"lat":50.868818,"lng":-1.9346172},"southwest":{"lat":50.85362689999999,"lng":-1.9478702}}},"place_id":"ChIJC21uTfK8c0gRVWfG433_kxM","types":["postal_code"]}],"status":"OK"}'
    
SELECT 
    JSON_Value (value,'$.types[0]') AS [Types], 
    JSON_Value (value,'$.long_name') AS [LongName]
FROM 
    OPENJSON (@jsondata,'$.results[0].address_components')
WHERE
    JSON_Value (value,'$.types[0]') = 'postal_code'

I am concerned that I am still relying on 'types[0]' and will be no further forward if there are multiple types.

user_14868997 · Answer

You need to use OPENJSON twice, once to unroll results, again to unroll address_components.

SELECT
  results.place_id,
  address_components.short_name
FROM OPENJSON(@json, '$.results')
  WITH (
    address_components nvarchar(max) AS JSON,
    place_id nvarchar(100)
  ) results
CROSS APPLY OPENJSON(results.address_components)
  WITH (
    short_name nvarchar(30),
    types0 nvarchar(max) '$.types[0]'
  ) address_components
WHERE address_components.types0 = 'postal_code';

If types can have postal_code in any position then you need to use OPENJSON again, this time in an IN or EXISTS subquery

SELECT
  results.place_id,
  address_components.short_name
FROM OPENJSON(@json, '$.results')
  WITH (
    address_components nvarchar(max) AS JSON,
    place_id nvarchar(100)
  ) results
CROSS APPLY OPENJSON(results.address_components)
  WITH (
    short_name nvarchar(30),
    types nvarchar(max) AS JSON
  ) address_components
WHERE 'postal_code' IN (
    SELECT value
    FROM OPENJSON(address_components.types) types);

db<>fiddle

When you use OPENJSON directly on an array with no property names, you get a table key (containing the index) and value.

SQL Server JSON retrieval based on specific selection criteria

1 Answers