在 SQL Server 2017 中以属性作为列返回所有 JSON 值和属性 [英] Return All JSON Values and Attributes with Attributes as Columns in SQL Server 2017

问题描述

使用一个只有两列(一个 id 和一个长 JSON 字符串)的表的简单示例，我如何轻松地返回所有 JSON 值和以属性为列的属性?

Using a simple example of a table with just two columns—an id and a long JSON string—how can I easily return all JSON values and attributes with the attributes as columns?

我知道我可以很容易地使用 JSON_VALUE 并指定一个特定的键/属性，但我不想为每个可能的键都列出一长串众多的 JSON_VALUE.有没有动态的方法来做到这一点?

I know I can readily use JSON_VALUE and specify a specific key/attribute, but I don’t want to have a long list of numerous JSON_VALUE for every possible key. Is there a dynamic way of doing this?

比如说，我有 100 个 JSON 键，结果应该是一个 id 列和另外 100 个键列及其值?

Say, for example, I have 100 JSON keys and the result should be an id column with another 100 key columns and their values?

谢谢.

推荐答案

如果你的 JSON 对象只是一个一级键值对列表，你可以调用 OPENJSON代码>:

If your JSON object is just a one-level list of key-value pairs, you can call OPENJSON:

DECLARE @mockup TABLE(Id INT, SomeJSON NVARCHAR(MAX));

INSERT INTO @mockup VALUES
 (1,N'{"key1":"value1","key2":"value2"}')
,(2,N'{"key1":"value1","key5":"value5"}')
,(3,N'{"keyA":"valueA","keyB":"valueB","keyZ":"valueZ"}');

SELECT t.Id
      ,A.*
FROM @mockup t
CROSS APPLY OPENJSON(t.SomeJSON) A;

结果

1   key1    value1  1
1   key2    value2  1
2   key1    value1  1
2   key5    value5  1
3   keyA    valueA  1
3   keyB    valueB  1
3   keyZ    valueZ  1

要以表格格式并排获得它，您可以使用 PIVOT、条件聚合或 - 可能最好 - WITH 子句:

To get this in tabular format side-by-side you can use PIVOT, conditional aggregation or - probably best - a WITH clause:

SELECT t.Id
      ,A.*
FROM @mockup t
CROSS APPLY OPENJSON(t.SomeJSON) 
WITH(key1 NVARCHAR(10)
    ,key2 NVARCHAR(10)
    ,key5 NVARCHAR(10)
    ,keyA NVARCHAR(10)
    ,keyB NVARCHAR(10)
    ,keyZ NVARCHAR(10)
    ) A;

结果

Id  key1    key2    key5    keyA    keyB    keyZ
1   value1  value2  NULL    NULL    NULL    NULL
2   value1  NULL    value5  NULL    NULL    NULL
3   NULL    NULL    NULL    valueA  valueB  valueZ

无论如何，您必须提前知道所有可能的密钥.这种方法可以实现最佳命名，并且您可以指定适当的类型.

In any case you must know all possible keys in advance. This approach allows for best naming and you can specify the appropriate type.

如果您事先不知道所有键，您可以查看动态 SQL.您可以使用上面的语句读取所有键并动态创建拟合语句/WITH 子句.

In case you don't know all keys in advance, you might look into dynamic SQL. You can use the statement above to read all keys and create the fitting statement/WITH-clause dynamically.

这篇关于在 SQL Server 2017 中以属性作为列返回所有 JSON 值和属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！