在SQL Server中查询JSON密钥名称 [英] Querying json key name in SQL Server

问题描述

像这样给出json ...

Given json like this...

{"setting1":"A","setting2":"B","setting3":"C"}

我希望看到类似...的结果

I would like to see results like...

+----------+-------+
|   name   | value |
+----------+-------+
| setting1 | A     |
| setting2 | B     |
| setting3 | C     |
+----------+-------+

我的难题是我试图找出如何提取密钥的名称(即"setting1"，"setting2"，"setting3"等)

My struggle is I'm trying to find out how to extract the key's name (i.e., "setting1", "setting2", "setting3", etc.)

我可以执行类似以下查询的操作，但是我不知道会有多少个设置以及它们的名称是什么，所以我想要更动态的东西.

I could do something like the following query, but I don't know how many settings there will be and what their names will be, so I'd like something more dynamic.

SELECT
    B.name,
    B.value
FROM OPENJSON(@json) WITH
    (
        setting1 varchar(50) '$.setting1',
        setting2 varchar(50) '$.setting2',
        setting3 varchar(50) '$.setting3'
    ) A
CROSS APPLY
    (
        VALUES
            ('setting1', A.setting1),
            ('setting2', A.setting2),
            ('setting3', A.setting3)
    ) B (name, value)

使用XML，我可以做一些简单的事情:

With XML, I could do something simple like this:

DECLARE @xml XML = '<settings><setting1>A</setting1><setting2>B</setting2><setting3>C</setting3></settings>'
SELECT
    A.setting.value('local-name(.)', 'VARCHAR(50)') name,
    A.setting.value('.', 'VARCHAR(50)') value
FROM @xml.nodes('settings/*') A (setting)

有什么方法可以做与SQL Server的json功能类似的事情?

Any way to do something similar with SQL Server's json functionality?

推荐答案

Aaron Bertrand在

Aaron Bertrand has written about json key value in Advanced JSON Techniques

SELECT x.[Key], x.[Value] 
FROM OPENJSON(@Json, '$') AS x;

返回

Key         Value
------------------
setting1    A
setting2    B
setting3    C

这篇关于在SQL Server中查询JSON密钥名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！