SQL Server如何从谷歌地图JSON中提取postal_code类型邮编

核心实现逻辑 #

不需要依赖任何数组下标定位，通过嵌套解析types数组全量匹配postal_code标识，完美适配谷歌地图API返回的组件顺序、types数组顺序不固定的场景。

完整可运行代码 #

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(addr_comp.value, '$.long_name') AS 邮编,
    JSON_VALUE(addr_comp.value, '$.short_name') AS 邮编简写
FROM 
    OPENJSON(@jsondata, '$.results[0].address_components') addr_comp
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(addr_comp.value, '$.types') type_items
    WHERE type_items.value = 'postal_code'
)

方案说明 #

• 外层OPENJSON先把address_components数组拆解为每行对应一个地址组件的结构
• 内层EXISTS子查询会将当前地址组件的types数组全量拆解扫描，只要数组任意位置存在postal_code值就命中匹配，完全不受数组下标影响
• 兼容SQL Server 2016及以上所有支持JSON功能的版本

内容的提问来源于stack exchange，提问作者Coilover