SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @json nvarchar(max) = N'
{
   "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
  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';

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);

place_id	short_name
ChIJC21uTfK8c0gRVWfG433_kxM	BH21 7JH

place_id	short_name
ChIJC21uTfK8c0gRVWfG433_kxM	BH21 7JH