sql server 2016 json 解析方法
前几天发现了sql server 2016支持了json 项目需要所以安装了 用了一下 方便了很多 ,写一下小笔记方便日后查看,也希望各位大神指正共同学习。
sql server 2016 安装图解网上很多,大家注意安装版本就可以了。 --1 带 Root Key select * from sys_menu for JSON AUTO ,ROOT('Result') --2 as select ID, Name, Age as [Entity.Age], Sex as [Entity.Sex] from Student for json path -- 3 输出有null列的 sql select * from sys_menu for JSON AUTO ,ROOT('SUSU'), include_null_values --4解析json --OPENJSON(将JSON文件转换为普通数据表) DECLARE @JSalestOrderDetails nVarChar(2000) = '{"OrdersArray": [ {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200}, {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100}, {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250}, {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200} ]}'; SELECT Number, Customer, Date, Quantity FROM OPENJSON (@JSalestOrderDetails, '$.OrdersArray') WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArray --5 ISJSON(判断是否是JSON格式) SELECT ISJSON('{"AAA":1}') --6 JSON_VALUE(分析JSON文件并提取出值) :取出json对象属性为Name的 列 SELECT JSON_VALUE('{"Order":{"Type":"C","Name":"张三"}}', '$.Order.Name')