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.

CodePudding user response：

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.