从Json数组中选择最后一个值 [英] Select last value from Json array

问题描述

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

I need to get the last item in an array from a JSON format. I have this 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"
        }
    }

我如何获得那个结果?

推荐答案

您可以尝试

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.

Some explanation

FROM OPENJSON will dive into your JSON and find the top-level elements. We fetch solution as is and we state AS JSON for options to specify, that this value will be treated as JSON later on.

CROSS APPLY将再次调用OPENJSON，但这一次是针对options的，我们将获得一个数组. key列是数组中的序数位置，因此我们可以使用TOP 1和ORDER BY key DESC来获取数组中的最后一个元素.

The CROSS APPLY will call OPENJSON again, but this time against the options and we will get an array back. The column key is the ordinal position within the array, so we can use TOP 1 and ORDER BY key DESC to get the last element in the array.

在将查询重新组合为JSON之前，必须将B.value用JSON_QUERY()包装.否则，我们会在JSON中看到转义字符，例如\t或\r\n.原因:如果没有此字符串，则JSON字符串将不会被当作JSON字符串，而会被当作其他字符串，并会作为一个单独的文本值打包到结果中.

Before we can re-assemble this query to a JSON we have to wrap B.value with JSON_QUERY(). Otherwise we would see escaped characters like \t or \r\n within the JSON. The reason: Without this, the JSON-string would not be taken as a JSON-string but as any other string and would be packed into the result as one single text value.

这篇关于从Json数组中选择最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！