代码之家 › 专栏 › 技术社区 › Luis felipe De jesus Munoz

从Json数组中选择最后一个值

sql-server-2016 tsql sql-server json

Luis felipe De jesus Munoz · 技术社区 · 6 年前

我需要从JSON格式获取数组中的最后一项。我有这个JSON。

  @json =   
  N'{ 
        "solution": "xxxxxxxxxxxxxxxxxxxxx",
        "options": [
            {
                "choice_id": 205073,
                "choice": "aaaa"
            },
            {
                "choice_id": 205074,
                "choice": "bbbb"
            },
            {
                "choice_id": 205075,
                "choice": "cccc"
            },
            {
                "choice_id": 205076,
                "choice": "dddd"
            }
        ],
    }'

我想得到

  @json =   
  N'{ 
        "solution": "xxxxxxxxxxxxxxxxxxxxx",
        "options": {
              "choice_id": 205076,
              "choice": "dddd"
        }
    }

我怎样才能达到这个结果?

3 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

你可以试试这个

DECLARE @json NVARCHAR(MAX) =   
N'{ 
    "solution": "xxxxxxxxxxxxxxxxxxxxx",
    "options": [
        {
            "choice_id": 205073,
            "choice": "aaaa"
        },
        {
            "choice_id": 205074,
            "choice": "bbbb"
        },
        {
            "choice_id": 205075,
            "choice": "cccc"
        },
        {
            "choice_id": 205076,
            "choice": "dddd"
        }
    ]
}';

SELECT TOP 1 
         A.solution
        ,JSON_QUERY(B.[value]) AS options
FROM OPENJSON(@json) WITH (solution nvarchar(max), options NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.options) B
ORDER BY B.[key] DESC
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

结果呢

{"solution":"xxxxxxxxxxxxxxxxxxxxx"
,"options":{
            "choice_id": 205076,
            "choice": "dddd"
           }
}

一些解释

FROM OPENJSON 将深入您的JSON并找到顶级元素。我们去拿 solution 原样我们声明 AS JSON options 要指定,此值稍后将被视为JSON。

这个 CROSS APPLY 将呼叫 OPENJSON 又一次,但这次反对 选项 我们会得到一个数组。列 key TOP 1 ORDER BY key DESC 获取数组中的最后一个元素。

B.value 具有 JSON_QUERY() . 否则我们会看到像这样的转义字符 \t 或 \r\n

David Browne - Microsoft 6 年前

您可以使用OPENJSON分解并为JSON重新组装文档(参见@digital.aron's answer),也可以使用JSON\u MODIFY,如下所示:

 declare @json nvarchar(max) =   
  N'{ 
        "solution": "xxxxxxxxxxxxxxxxxxxxx",
        "options": [
            {
                "choice_id": 205073,
                "choice": "aaaa"
            },
            {
                "choice_id": 205074,
                "choice": "bbbb"
            },
            {
                "choice_id": 205075,
                "choice": "cccc"
            },
            {
                "choice_id": 205076,
                "choice": "dddd"
            }
        ]
    }'  


declare @options nvarchar(max) = (
    select top 1 value 
    from openjson(@json,'$.options')
    order by [key] desc 
);

set @json = json_modify(@json, '$.options', json_query(@options))

select  @json

输出

{ 
        "solution": "xxxxxxxxxxxxxxxxxxxxx",
        "options": {
                "choice_id": 205076,
                "choice": "dddd"
            }
    }

JSON_QUERY 函数还用于将字符串解析为JSON片段。否则,@options中的值将作为单个字符串值而不是json对象插入。

digital.aaron 6 年前

首先,JSON字符串的格式不正确。你需要去掉右括号后面的逗号。

让我们声明变量:

DECLARE @json NVARCHAR(MAX) = N'{ 
        "solution": "xxxxxxxxxxxxxxxxxxxxx",
        "options": [
            {
                "choice_id": 205073,
                "choice": "aaaa"
            },
            {
                "choice_id": 205074,
                "choice": "bbbb"
            },
            {
                "choice_id": 205075,
                "choice": "cccc"
            },
            {
                "choice_id": 205076,
                "choice": "dddd"
            }
        ]
    }'

OPENJSON() 和 FOR JSON

SELECT jsonfield = CAST((
                            SELECT TOP 1
                                j.solution
                                ,o.choice_id
                                ,o.choice
                            FROM
                                OPENJSON(@json)
                                WITH
                                (
                                    solution VARCHAR(MAX) '$.solution'
                                    ,options NVARCHAR(MAX) '$.options' AS JSON
                                ) j
                            CROSS APPLY
                                OPENJSON(options)
                                WITH
                                (
                                    choice_id INT '$.choice_id'
                                    ,choice VARCHAR(4) '$.choice'
                                ) o
                            ORDER BY o.choice_id DESC
                            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                        ) AS NVARCHAR(MAX))

编辑:

如果不想按任何节点值排序,也可以使用此查询:

SELECT jsonfield = CAST((
                            SELECT TOP 1
                                j.solution
                                --,o.*
                                ,c.choice_id
                                ,c.choice
                            FROM OPENJSON(@json)
                            WITH
                                (
                                    solution VARCHAR(MAX) '$.solution'
                                    ,options NVARCHAR(MAX) '$.options' AS JSON
                                ) j
                            CROSS APPLY OPENJSON(options) o
                            CROSS APPLY OPENJSON(o.Value) 
                            WITH (
                                    choice_id int '$.choice_id',
                                    choice varchar(4) '$.choice'
                                    ) c
                            ORDER BY [key] DESC
                            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                        ) AS NVARCHAR(MAX))