SQL中的 JSON -获得所有嵌套键的最佳方式

Question

我正在MS SQL中使用JSON，并试图找出获得所有键/值对的最佳方法?我在Azure Data Studio中拉了一行，它为我更好地查看格式数据，但是是否有更好的方法或sql代码来显示所有键/值对?

我在网上找到的例子显示了类似于下面的代码，它收回了值，但如果我有20多个键，有没有比输入每个键更好的方法?此外，如果在我不知情的情况下添加了一组额外的数据，会发生什么?能够循环遍历这些关键项不是很有帮助吗?关于如何为SSMS提取所有这些信息有什么建议吗?

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
JSON_VALUE(f.doc, '$.address.city') AS City,
JSON_VALUE(f.doc, '$.address.county') AS County

Answer 1

注意:静态SQL不能根据数据有动态的列数。

要么…

• 规范化数据，这样您将添加行而不是列(根据此答案)
• 只能使用固定数量的列
• 使用DynamicSQL(代码读取数据，写入非SQL，然后执行新的SQL)

---

要真正通用，并容纳任意数量的键，您可以递归地打开json并创建一个新的ROW

样本数据:

CREATE TABLE example (
row_id  INT,
json    NVARCHAR(MAX)
);
INSERT INTO
example
VALUES
(1, N'{"id": 1, "address": {"city": "city_1", "county": "county_1"}}'),
(2, N'{"id": 2, "address": {"city": "city_2", "county": "county_2", "nation": "uk"}}'),
(3, N'{"id": 3, "an_array": [{"city": "city_3"},999,"smeg"]}')

代码:

WITH
recursed_open_json AS
(
SELECT
example.row_id,
open_json.*
FROM
example
CROSS APPLY
OPENJSON(json) AS open_json
UNION ALL

SELECT
r.row_id,
CASE WHEN r.type = 4
THEN r.[key] + '[' + o.[key] + ']'
ELSE r.[key] + '.' + o.[key]       END,
o.value,
o.type
FROM
recursed_open_json   AS r
CROSS APPLY
OPENJSON(r.value) AS o
WHERE
r.type IN (4, 5)
)
SELECT
*
FROM
recursed_open_json
WHERE
type NOT IN (4, 5)
ORDER BY
row_id,
[key]

结果:

<表类>row_id键价值类型tbody><<tr>1address.citycity_111address.countycounty_111id122address.citycity_212address.countycounty_212address.nation英国12id223an_array [0] .citycity_313an_array [1]99923an_array [2]smeg13id32