如何在SQL Server中反转OPENJSON()函数？

Question

SQL Server的OPENJSON()函数可以将JSON数组转换为带有键值对的SQL表格，例如：

DECLARE @json NVARCHAR(MAX);
SET @json = '{
    "key1": "val1",
    "key2": "val2",
    "key3": "val3"
    }';

SELECT * FROM OPENJSON(@json, '$')

结果：

key     value   type
--------------------
key1    val1    1
key2    val2    1
key3    val3    1

什么是将此键/值表转换回JSON数组的最佳通用方法？

为什么？如果我们可以用一个函数实现这个目标，它就会打开一系列在SQL Server上不可能实现的JSON修改，例如：

- 重新排列元素 - 重命名属性（键名） - 将JSON数组拆分成较小的数组/组合JSON数组 - 比较JSON数组（哪些键/值元素存在于两个JSON中？有什么差异？） - 清理JSON（删除语法空格/换行符以压缩它）

现在，我可以开始做简单的CONCAT('"',[key],'":"',[value])，然后做逗号列表聚合。但是，如果我想要一个易于应用于我的代码库并且适用于所有数据类型的代码，这就不是一项简单的任务。通过查看json格式定义，转换应该考虑以下因素：a）6种不同的数据类型，b）转义字符，c）SQL NULL/json null处理，d）我可能忽略的因素。至少，应支持下面的示例：

DECLARE @test_json NVARCHAR(MAX);
SET @test_json = '{
    "myNull": null,
    "myString": "start_\\_\"_\/_\b_\f_\n_\r_\t_\u2600_stop",
    "myNumber": 3.14,
    "myBool": true,
    "myArray": ["1", 2],
    "myObject": {"key":"val"}
    }'
SELECT * FROM OPENJSON(@test_json, '$')

结果：

key         value                           type
------------------------------------------------
myNull      NULL                            0
myString    start_\_"_/___ _ _ _☀_stop      1
myNumber    3.14                            2
myBool      true                            3
myArray     ["1", 2]                        4
myObject    {"key":"val"}                   5

对于字符串聚合部分，我们长期以来一直遭受着'FOR XML PATH'的痛苦。幸运的是，在SQL2017 / AzureDB上有STRING_AGG()函数，我将接受基于此函数的解决方案。

- Martin Thøgersen

---

该函数当然应该接受参数 (key nvarchar(4000), value nvarchar(max), type int)，其中 type 的取值范围为 0 到 5。 - Martin Thøgersen

这是一个老问题，但很有趣(+1)。当我处于类似的情况时，我使用了以下解决方案（当然不是作为UDF，并基于问题中的JSON）：SELECT CONCAT('{', STRING_AGG(t.Pair, ',') ,'}') FROM (SELECT CONCAT('"', [key], '":', CASE WHEN [type] = 0 THEN 'null' WHEN [type] = 1 THEN CONCAT('"', STRING_ESCAPE([value], 'json'), '"') WHEN [type] = 2 THEN [value] WHEN [type] = 3 THEN [value] WHEN [type] = 4 THEN JSON_QUERY([value]) WHEN [type] = 5 THEN JSON_QUERY([value]) END) AS Pair FROM OPENJSON(@test_json, '$')) t。 - Zhorov

@Zhorov 我最终也写了一些带有CASE的自定义函数。随意发表回答。 - Martin Thøgersen

Answer 1

我使用string_agg

declare @json table ( Name varchar(80), Value varchar(max) )

insert into @json 
    select [Key], Value from openjson(@attributes)

insert into @json values ( 'name', @name )
insert into @json values ( 'title', @title )
insert into @json values ( 'description', @description )

set @attributes = '{' + (select STRING_AGG( '"' + Name + '":"' + 
    REPLACE (value, '"', '\"' ) +'"', ',') from @json) + '}'

- Rainer Prem

Answer 2

您可以使用FOR JSON命令完成此操作。

select * from table for json auto

我的结果：

[{"LogId":1,"DtLog":"2017-09-30T21:04:45.6700000","FileId":1}, {"LogId":2,"DtLog":"2017-09-30T21:08:35.8633333","FileId":3},{"LogId":3,"DtLog":"2017-09-30T21:08:36.4433333","FileId":2},{"LogId":4,"DtLog":"2017-09-30T21:08:36.9866667","FileId":12},{"LogId":5,"DtLog":"2017-09-30T21:15:22.5366667","FileId":13},{"LogId":6,"DtLog":"2017-09-30T21:38:43.7866667","FileId":17}]

- Thiago Loureiro

---

谢谢，但这不起作用。主要是因为“value”字段都将被转换回文本。您无法考虑“类型”。 - Martin Thøgersen