json - SQL Server - OPENJSON 解析 JSON 文本
问题描述
JSON
我在定义为的列中有以下文本nvarchar(max)
:
{"country":
{"id":642,
"name":"ROMANIA",
"nameEn":"ROMANIA",
"isoAlpha2":"RO",
"isoAlpha3":"ROU",
"currencyCode":"RON",
"requireState":false,
"addressType":1,
"streetTypes":
[
{"name":"str.","nameEn":"str."},
{"name":"","nameEn":""},
{"name":"ale.","nameEn":"ale."},
{"name":"int.","nameEn":"int."},
{"name":"fdt.","nameEn":"fdt."},
{"name":"pta.","nameEn":"pta."},
{"name":"bld.","nameEn":"bld."},
{"name":"drm.","nameEn":"drm."},
{"name":"cal.","nameEn":"cal."},
{"name":"sos.","nameEn":"sos."}
],
"quarterTypes":[],
"postCodeFormats":["","NNNNNN"]
}
}
我想提取 ID 的值,即 642,我使用了以下语句,但我想知道这是否是正确的方法:
SELECT TOP 1 VALUE
FROM OPENJSON(@jsonBody, '$.country')
如何在不使用的情况下仅提取 id 元素的值TOP 1
?
解决方案
更精确的方法是实际表明您想要返回 ID,而不是假设它始终是第一个值。
SELECT *
FROM OPENJSON(@, N'$.country')
WITH (id int '$.id')
推荐阅读
- bash - How do i delay or buffer the stream of a programs output for a few seconds before it pipes to another program?
- spacy - Select specific entities to be tagged
- php - 通过文件共享在 IIS 中加载 PHP 的长时间延迟
- vb.net - 我无法使用 OpenFileDialog 打开个性化扩展
- javascript - How to make one div always be at the bottom of the view and make top div resize based on bottom div?
- java - 如何使用 Selenium Webdriver 点击元素
- objective-c - What's the difference between id
and MTLDevice*? - python - 如何打开不在库根目录中的 hdf5
- c++ - C++ 跳过了 cin 行
- php - 奇怪的“空测试套件”。在 Docker 中运行 PHPUnit 测试时的输出