SQL Server – Work with JSON

2022-11-09 14:03:52

前言

JSON 是一个很好的格式, array, object 就能表达一个表格了.

如果想保存一些结构格式, 又不想用表格这么大费周章的话, JSON 会是很好选择.

比如我用它来记入 Audit Trial, 每一个请求的 post data 直接记入到一个 column 里面.

SQL Server 是在 2016 版本开始支持 JSON 格式的.

之前写过一些小笔记

主要参考

JSON data in SQL Server

Index JSON data

实战

创建 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 或者是索引来优化.

具体看这篇, 我就不写了.

码农公寓

前言