时间戳

首页 > 解决方案 > 如何从 json 中提取这些值

json - 如何从 json 中提取这些值

问题描述

鉴于此json：

{
    "type": "https://tools.ietf.org/html/rfc7231#section-6.5.1",
    "title": "One or more validation errors occurred.",
    "status": 400,
    "traceId": "|a134a743-4f46942d175af9d6.",
    "errors": {
        "$.payment[0].invoiceDate": [
            "The JSON value could not be converted to System.Nullable`1[System.Int32]. Path: $.payment[0].invoiceDate | LineNumber: 0 | BytePositionInLine: 533."
        ]
    }
}

我怎样才能提取"$.payment[0].invoiceDate"和"The JSON value..." 转换成变量？

使用 SQL Server 2016。

标签： jsonsql-server

解决方案

这里有3种不同的方式

declare @json           nvarchar(max)=N'{
    "type": "https://tools.ietf.org/html/rfc7231#section-6.5.1",
    "title": "One or more validation errors occurred.",
    "status": 400,
    "traceId": "|a134a743-4f46942d175af9d6.",
    "errors": {
        "$.payment[0].invoiceDate": [
            "The JSON value could not be converted to System.Nullable`1[System.Int32]. Path: $.payment[0].invoiceDate | LineNumber: 0 | BytePositionInLine: 533."
        ]
    }
}';

/* #1 without OPENJSON */
select json_value(@json, '$.errors."$.payment[0].invoiceDate"[0]') newCol;

/* #2 with OPENJSON and JSON_VALUE */
select json_value(value, '$[0]') newCol
from openjson(@json,'$.errors');

/* #3 with OPENJSON */
select value newCol
from openjson(@json,'$.errors."$.payment[0].invoiceDate"');

输出（每个都是一样的）

newCol
The JSON value could not be converted to System.Nullable`1[System.Int32]. Path: $.payment[0].invoiceDate | LineNumber: 0 | BytePositionInLine: 533.

推荐阅读