sql - 从 JSON 访问数组记录和普通记录
问题描述
我是处理 json 记录的新手。我有一个json记录如下:
{"responseobj":{"ResponseCode":"0","ResponseDesc":"Success"},
"objDropdown":[{"Master_id":0,"ID":"en","Value":"English","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"fr","Value":"français","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"it","Value":"italiano","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"pt","Value":"Português","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"es","Value":"español","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"de","Value":"Deutsch","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"nl","Value":"Nederlands","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
]}'
请教我如何从中获取记录。
解决方案
您可以使用 OpenJSON() - SQL server 2016 及更高版本执行此操作:
DECLARE @json VARCHAR(4000);
SET @json
= '{"responseobj":{"ResponseCode":"0","ResponseDesc":"Success"},
"objDropdown":[{"Master_id":0,"ID":"en","Value":"English","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"fr","Value":"français","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"it","Value":"italiano","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"pt","Value":"Português","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"es","Value":"español","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"de","Value":"Deutsch","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
,{"Master_id":0,"ID":"nl","Value":"Nederlands","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
]}';
SELECT *
FROM OPENJSON(@json, '$.objDropdown')
WITH
(
Master_id INT '$.Master_id',
ID VARCHAR(5) '$.ID',
[Value] NVARCHAR(50) '$.Value',
Selected BIT '$.Selected',
IsActive BIT '$.IsActive',
[Description] NVARCHAR(4000) '$.Description',
TopupId INT '$.TopupId',
TopupAmount INT '$.TopupAmount'
);
编辑:获取 responseobj 更简单:
SELECT *
FROM OPENJSON(@json, '$.responseobj');
推荐阅读
- python - 如何让主函数调用其他程序
- excel - 修改评论时如何触发宏?
- javascript - 在 iFrame SRC 中更改频道名称
- google-maps-api-3 - 如何将 API 密钥应用于 Google Maps Tile 服务器 URL?
- java - MarkLogic Java API PlanBuilderBase.ExportablePlanBase
- google-apps-script - 使用 MailApp.sendEmail 谷歌表格防止文本换行?谷歌脚本
- java - 正则表达式中的通配符仅在停止词之前是贪婪的
- typescript - 确保静态键存在于另一个类上
- r - R shinydashboardplus 翻转框 - 如何删除图像
- javascript - 提取库到单独的文件