我过去就是这样处理这个问题的。您可以在ssms中运行：

DECLARE @json AS VARCHAR(1000) = '{ "results":[ { "address_components":[
    { "long_name":"Khalifa City", "short_name":"Khalifa City", "types":[ "political", "sublocality", "sublocality_level_1" ] },
    { "long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":[ "locality", "political" ] },
    { "long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":[ "administrative_area_level_1", "political" ] },
    { "long_name":"United Arab Emirates", "short_name":"AE", "types":[ "country", "political" ] }
] } ], "status":"OK" }';

SELECT
    Addresses.long_name, Addresses.short_name, Addresses.[types]
FROM OPENJSON ( @json, '$.results' ) WITH (
    addresses NVARCHAR(MAX) '$.address_components' AS JSON
) AS j
CROSS APPLY (

    SELECT * FROM OPENJSON ( j.addresses ) WITH (
        long_name VARCHAR(50) '$.long_name',
        short_name VARCHAR(50) '$.short_name',
        [types] NVARCHAR(MAX) '$.types' AS JSON
    ) AS Names
    CROSS APPLY OPENJSON ( [types] ) AS [Types]
    WHERE [Types].[value] = 'administrative_area_level_1'

) AS Addresses;

退货

+-----------+------------+------------------------------------------------+
| long_name | short_name |                     types                      |
+-----------+------------+------------------------------------------------+
| Abu Dhabi | Abu Dhabi  | [ "administrative_area_level_1", "political" ] |
+-----------+------------+------------------------------------------------+

如果我理解了这个问题，而您想要解析输入json（即使 $.results json数组有多个项），以下方法可能会有所帮助：
json码：

DECLARE @json nvarchar(max) = N'{
   "results":[
      {
         "address_components":[
            {"long_name":"Khalifa City", "short_name":"Khalifa City", "types":["political", "sublocality", "sublocality_level_1"]},
            {"long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":["locality", "political"]},
            {"long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":["administrative_area_level_1", "political"]},
            {"long_name":"United Arab Emirates", "short_name":"AE", "types":["country", "political"]}
         ]
      }
   ],
   "status":"OK"
}'

声明：

SELECT j2.long_name, j2.short_name
FROM OPENJSON(@json, '$.results') j1
CROSS APPLY OPENJSON(j1.value, '$.address_components') WITH (
   long_name varchar(100) '$.long_name',
   short_name varchar(100) '$.short_name',
   types nvarchar(max) '$.types' AS JSON
) j2
CROSS APPLY OPENJSON(j2.types) j3
WHERE j3.[value] = 'administrative_area_level_1'

输出：

long_name   short_name
----------------------
Abu Dhabi   Abu Dhabi