正文
转换嵌套JSON数据为TABLE
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
先准备一些数据：
创建一张临时表来存储：
DECLARE @json_table AS TABLE
(
[type] NVARCHAR(MAX),
[desc] NVARCHAR(MAX)
)
Source Code
获取第一层数据：
INSERT INTO @json_table ([type],[desc])
SELECT [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
)
WHERE [type] IS NOT NULL;
Source Code
获取第二层DB_CLR节点的数据：
INSERT INTO @json_table ([type],[desc])
SELECT [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_CLR NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_CLR)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
);
Source Code
同样方法，获取第二层的DB_TABLE节点数据：
INSERT INTO @json_table ([type],[desc])
SELECT [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_TABLE NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_TABLE)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
) ;
Source Code
最后查询临时表存储表的数据：
但是，如果我们想加上节点root名称，用来真正区别记录的类别：
把临时表添加一个字段[Root]:
DECLARE @json_table AS TABLE
(
[root] NVARCHAR(MAX),
[type] NVARCHAR(MAX),
[desc] NVARCHAR(MAX)
);
Source Code
以上三个节点获取的源代码：
INSERT INTO @json_table ([root],[type],[desc])
SELECT [key],b.[type],[desc] FROM
OPENJSON (@json_text) a
CROSS APPLY
OPENJSON (@json_text,'$.DB')
WITH (
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
)b
WHERE b.[type] IS NOT NULL; INSERT INTO @json_table ([root],[type],[desc])
SELECT 'DB_CLR', [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_CLR NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_CLR)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
); INSERT INTO @json_table ([root],[type],[desc])
SELECT 'DB_TABLE', [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_TABLE NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_TABLE)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
) ;
Source Code
最后是查询结果：
最后再想修改一下，把3段SQL语句，使用UNION ALL串连起来：
INSERT INTO @json_table ([root],[type],[desc])
SELECT [key],b.[type],[desc] FROM
OPENJSON (@json_text) a
CROSS APPLY
OPENJSON (@json_text,'$.DB')
WITH (
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
)b
WHERE b.[type] IS NOT NULL --INSERT INTO @json_table ([root],[type],[desc])
UNION ALL SELECT 'DB_CLR', [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_CLR NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_CLR)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
) --INSERT INTO @json_table ([root],[type],[desc])
UNION ALL SELECT 'DB_TABLE', [type],[desc] FROM
OPENJSON (@json_text,'$.DB')
WITH (
DB_TABLE NVARCHAR(MAX) AS JSON
)
CROSS APPLY
OPENJSON (DB_TABLE)
WITH
(
[type] NVARCHAR(MAX) '$.type',
[desc] NVARCHAR(MAX) '$.desc'
) ;
Source Code
