SQL Server – Work with JSON
前言
JSON 是一个很好的格式, array, object 就能表达一个表格了.
如果想保存一些结构格式, 又不想用表格这么大费周章的话, JSON 会是很好选择.
比如我用它来记入 Audit Trial, 每一个请求的 post data 直接记入到一个 column 里面.
SQL Server 是在 2016 版本开始支持 JSON 格式的.
之前写过一些小笔记
主要参考
Format Query Results as JSON with FOR JSON (SQL Server)
实战
创建 JSON Column
CREATE TABLE TestJson ( Id int IDENTITY CONSTRAINT PK_TestJson_Id PRIMARY KEY, JsonData nvarchar(max) CONSTRAINT [Content should be formatted as JSON] CHECK(ISJSON(JsonData)>0) );
Json Column 其实就是 nvarchar 来的, 只是在上面加了一层 constraint 而已. ISJSON 是一个判断 text 是不是 JSON 的方法.
进资料
没什么特别的, 进 string 就可以了
INSERT INTO TestJson (JsonData) VALUES ('{ "name": "Derrick", "age": 11, "good": true }'), ('{ "name": "Keatkeat", "age": 12, "good": false }');
Query
SELECT JSON_VALUE(JsonData, '$.name')as [Name], JSON_VALUE(JsonData, '$.age') as Age, CAST(JSON_VALUE(JsonData, '$.good') as bit) as Good, * FROM TestJson WHERE JSON_VALUE(JsonData, '$.good') = 'true' ORDER BY JSON_VALUE(JsonData, '$.name');
通过 JSON_VALUE 这个方法就可以获取到对象属性值, 然后就可以当成普通 column 值来处理了.
注: boolean 被 parse 出来是 nvarchar 哦, 可以通过 cast 换成 bit. number parse 出来时 int, 这个是对的.
Update
UPDATE TestJson SET JsonData = JSON_MODIFY(JsonData, '$.age', 100);
通过 JSON_MODIFY 就可以修改 JSON 内容了, 它会返回一个 JSON string, 有一种 immutable 的 feel...
如果要同时修改多个属性值也非常简单
UPDATE TestJson SET JsonData = JSON_MODIFY(JSON_MODIFY(JsonData, '$.age', 100), '$.good', 'false') ;
只要嵌套一下就可以了哦....
JSON Array
TODO...
JSON Index
JSON 要 filter, orderby 快的话, 做法是通过 computed column 或者是索引来优化.
具体看这篇, 我就不写了.
Format to Json
把 query result serialize to json
SELECT TOP 1 * FROM Country FOR JSON AUTO, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER;
{"CountryId":6,"Name":"Malaysia122"}
AUTO 是一种 mode, 它会依据你的 join 把 child table 变成一个 child array, 或者 parent table 变成 object 哦. 很聪明的
还有一种叫 PATH 的 mode. 就是可以通过 Select Name as 'Address.Name' 这样去自定义, 然后出来的 result 就会有层次.
默认 null value 就不会有 property,这个通常不是我们要的啦, 所以要加 INCLDE_NULL_VALUES
如果我们知道 result 是 single row, 那么可以加一个 WITHOUT_ARRAY_WRAPPER.
转 https://www.cnblogs.com/keatkeat/p/15428425.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战