sqlserver2016 openjson(自学备案2)_sqlserver openjson-CSDN博客

本文链接：https://blog.csdn.net/xl502689459/article/details/103495672

本文详细介绍如何在SQL中处理JSON数据，包括验证、修改、查询及与数据库交互的方法。涵盖兼容性设置、限定列格式、生成及解析JSON，以及利用内置函数如ISJSON、JSON_VALUE进行数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-------openjson不工作–
-----（解决方案：ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130） – database_name 数据库名称----

-----限定列为json–
—ALTER TABLE Logs ADD CONSTRAINT [Log record should be formatted as JSON] CHECK (ISJSON(log)=1) —Logs.log 数据必须为josn执行成功或者delete掉数据

---- 生成json

–root 指定根节点
–INCLUDE_NULL_VALUES JSON 输出中添加 NULL 值

select top 10 deal_id ,pull_id ,pull_date
from pull_cloud_deal_list for json Path ,root (‘data’), INCLUDE_NULL_VALUES ;

select top 10 deal_id ,pull_id as ‘info.pull_id’,pull_date as ‘info.pull_date’
from pull_cloud_deal_list for json Path;

------- WITHOUT_ARRAY_WRAPPER 从 JSON 中删除方括号
–单行结果
SELECT 2015 as year, 12 as month, 15 as day
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
指定：{ “year”: 2015, “month”: 12, “day”: 15}
未指定：[{ “year”: 2015, “month”: 12, “day”: 15} ]

----更改json 值
DECLARE @json NVARCHAR(MAX);
SET @json = ‘{“status”:"",“info”:{“address”: [{“town”: “Belgrade”}, {“town”: “Paris”}, {“town”:“Madrid”}]}}’;
SET @json = JSON_MODIFY(@json, ‘ $info.address[1].town', 'London'); set @json= JSON_MODIFY(@json ,'$ .status’,‘00’)
SELECT modifiedJson = @json;

----json 呈现表格
DECLARE @json NVARCHAR(MAX);
SET @json = N’[
{“id”: 2, “info”: {“name”: “John”, “surname”: “Smith”}, “age”: 25},
{“id”: 5, “info”: {“name”: “Jane”, “surname”: “Smith”}, “dob”: “2005-11-04T12:00:00”}
]’;

----数组json呈现结果 OUTER APPLY OPENJSON
DECLARE @json NVARCHAR(MAX);
SET @json = N’[
{“id”: 2, “info”: {“name”: " ", “surname”: “Smith”}, “age”: 25},
{“id”: 5, “info”: {“name”: “Jane”, “surname”: “Smith”, “skills”: [“SQL”, “C#”, “Azure”]}, “dob”: “2005-11-04T12:00:00”}
]’;

SELECT *
FROM OPENJSON(@json)
WITH (
id INT ‘strict $. i d^{'}, f i r s t N a m e N V A R C H A R (50)^{'}$ .info.name’,
lastName NVARCHAR(50) ‘ $. i n f o . s u r n a m e^{'}, a g e I N T, d a t e O f B i r t h D A T E T I M E 2^{'}$ .dob’,
skills NVARCHAR(MAX) ‘ $. i n f o . s k i l l s^{'} A S J S O N) O U T E R A P P L Y O P E N J S O N (s k i l l s) W I T H (s k i l l N V A R C H A R (8)^{'}$ ’);

-------数组嵌套列表呈现表格-----------------------------
DECLARE @json NVARCHAR(MAX);
SET @json = ‘{“status”:“111”,“info”:{“address”: [{“town”: “Belgrade”,“one”:“11111”}, {“town”: “Paris”}, {“town”:“Madrid”}]}}’;

SELECT *
FROM OPENJSON(@json)
WITH (
status NVARCHAR(20) ’ $. s t a t u s^{'}, s k i l l s N V A R C H A R (M A X)^{'}$ .info.address’ AS JSON
)
OUTER APPLY OPENJSON(skills)
WITH (town NVARCHAR(MAX) ‘ $. t o w n^{'}, o n e N V A R C H A R (M A X)^{'}$ .one’ );

-------json 与数据交互----------

create table Logs (
_id bigint primary key identity,
log nvarchar(max),

severity AS JSON_VALUE(log, '$.severity'),
index ix_severity (severity)

);
–创建 Logs severity 根据log[‘severity’] 赋值
– index ix_severity (severity) 增加索引便于查询

-----配置 table Logs 列 log 的数据必须为json校验
ALTER TABLE Logs ADD CONSTRAINT [Log record should be formatted as JSON] CHECK (ISJSON(log)=1)

insert into Logs (log) values(’{“severity”:“aaaaaa”}’)

select * from logs
select log from Logs where severity =‘aaaaaa’
–drop table Logs

-----内存优化表内存表慎重使用 ---------------
----内存优化表最适合用于频繁更改的文档。考虑内存优化表时，还要考虑性能。如果可能，为内存优化集合中的 JSON 文档使用 NVARCHAR(4000) 而不是 NVARCHAR(max)，------
----将经典集合转换为内存优化集合只需执行一个操作，即在表定义后指定 with (memory_optimized=on) 选项，可获得内存优化版的 JSON 集合
create table Logs (
_id bigint identity primary key nonclustered,
log nvarchar(4000),
severity AS JSON_VALUE(log, ‘$.severity’) ,
index ix_severity (severity)
) with (memory_optimized=on)

–建立内存表，存储过程修改-
–CREATE PROCEDURE UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
–WITH SCHEMABINDING, NATIVE_COMPILATION
–AS BEGIN
– ATOMIC WITH (transaction isolation level = snapshot, language = N’English’)
– UPDATE Logs
– SET log = JSON_MODIFY(log, @Property, @Value)
– WHERE _id = @Id;
–END

–json文件导入 OPENROWSET(BULK)—
–[ { “id” : “978-0641723445”, “cat” : [“book”,“hardcover”], “name” : “The Lightning Thief”, “author” : “Rick Riordan”, “series_t” : “Percy Jackson and the Olympians”, “sequence_i” : 1, “genre_s” : “fantasy”, “inStock” : true, “price” : 12.50, “pages_i” : 384 } , { “id” : “978-1423103349”, “cat” : [“book”,“paperback”], “name” : “The Sea of Monsters”, “author” : “Rick Riordan”, “series_t” : “Percy Jackson and the Olympians”, “sequence_i” : 2, “genre_s” : “fantasy”, “inStock” : true, “price” : 6.49, “pages_i” : 304 } , { “id” : “978-1857995879”, “cat” : [“book”,“paperback”], “name” : “Sophie’s World : The Greek Philosophers”, “author” : “Jostein Gaarder”, “sequence_i” : 1, “genre_s” : “fantasy”, “inStock” : true, “price” : 3.07, “pages_i” : 64 } , { “id” : “978-1933988177”, “cat” : [“book”,“paperback”], “name” : “Lucene in Action, Second Edition”, “author” : “Michael McCandless”, “sequence_i” : 1, “genre_s” : “IT”, “inStock” : true, “price” : 30.50, “pages_i” : 475 } ]–
SELECT BulkColumn
FROM OPENROWSET (BULK ‘C:\cky\json\books.json’, SINGLE_CLOB) as j;

------OPENROWSET 从文件中读取单个文本值。 OPENROWSET 将值作为 BulkColumn 返回，并将 BulkColumn 传递给 OPENJSON 函数。 OPENJSON 循环访问 BulkColumn 数组中 JSON 对象的数组，并在每行中返回一本书籍。每行格式为 JSON---------
SELECT value
FROM OPENROWSET (BULK ‘C:\cky\json\books.json’, SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)

-----字段呈现列-----
SELECT book.* , BulkColumn into json_test_book
FROM OPENROWSET (BULK ‘C:\cky\json\books.json’, SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( id nvarchar(100), cat NVARCHAR(MAX) AS JSON , name nvarchar(100), price float,
pages_i int, author nvarchar(100) ) AS book
OUTER APPLY OPENJSON(cat)
WITH (town NVARCHAR(8) ‘$’);

----对 JSON 数据编制索引
–JSON_VALUE 获取json JSON_VALUE(log, ‘$.severity’)
–CREATE INDEX 索引名 ON 库名.表名(列名) 通过索引
–CREATE INDEX 索引名库名.表名(列名) INCLUDE(SalesOrderNumber,OrderDate) --通过包含的列进一步优化索引

–JSON 的内置支持包括本主题简要介绍的下列内置函数。
–ISJSON 测试字符串是否包含有效 JSON。
–JSON_VALUE 从 JSON 字符串中提取标量值。
–JSON_QUERY 从 JSON 字符串中提取对象或数组。
–JSON_MODIFY 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。

SELECT * FROM Families WHERE ISJSON(doc) > 0

SELECT JSON_VALUE(f.doc, ‘ $id') AS Name, JSON_VALUE(f.doc, '$ .address.city’) AS City,
JSON_VALUE(f.doc, ‘$.address.county’) AS County
FROM Families f
WHERE JSON_VALUE(f.doc,N’lax $id') = '111' --"JSON_VALUE or JSON_QUERY" 的自变量 2 必须为字符串参数。 ORDER BY JSON_VALUE(f.doc, '$ .address.city’) DESC, JSON_VALUE(f.doc, ‘$.address.state’) ASC

–默认显示架构–
SELECT [key],value
FROM OPENJSON(’[“en-GB”, “en-UK”,“de-AT”,“es-AR”,“sr-Cyrl”]’)
SELECT *
FROM OPENJSON(’{“name”:“John”,“surname”:“Doe”,“age”:45}’)

DECLARE @json NVARCHAR(MAX)
SET @json=N’{
“info”:{
“type”:1,
“address”:{
“town”:“Bristol”,
“county”:“Avon”,
“country”:“England”
},
“tags”:[“Sport”, “Water polo”]
},
“type”:“Basic”
}’
SELECT *
FROM OPENJSON(@json,N’lax $.info’)