使用 JSON_VALUE 访问 SQL Server 2016 中的 JSON 数组

Question

我在使用新引入的 JSON_VALUE 函数访问 json 中的数组时被卡住了。请考虑以下代码 -

 IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData')
    DROP TABLE JsonData;
go

CREATE TABLE JsonData(JsonData nvarchar(max));
DECLARE @SQL nvarchar(max);
DECLARE @Table AS TABLE(JsonPath VARCHAR(256));

INSERT INTO JsonData(JsonData)
VALUES(
'{
  "firstName": "John",
  "lastName" : "doe",
  "age"      : 26,
  "address"  : {
    "streetAddress": "naist street",
    "city"         : "Nara",
    "postalCode"   : "630-0192"
  },
  "phoneNumbers": [
    {
      "type"  : "iPhone",
      "number": "0123-4567-8888"
    },
    {
      "type"  : "home",
      "number": "0123-4567-8910"
    }
  ]
}')

INSERT INTO @Table
SELECT VALUE  FROM OPENJSON('{
"Path1":"$.firstName","Path2":"$.phoneNumbers[:1].number"
}') ;

SELECT  @SQL=(SELECT 'UNION SELECT '''+ CAST(JsonPath AS VARCHAR(256)) +''',JSON_VALUE(JsonData,'''+a.JsonPath+''')
                    FROM JsonData a'
                    FROM @Table a
        FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)')
FROM @Table t;

SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)

PRINT @SQL

EXEC SP_EXECUTESQL @SQL;

在这里，如果我想访问特定的电话号码，那么访问此节点的常规语法不起作用。在这种情况下，我收到以下错误

JSON path is not properly formatted. Unexpected character ':' is found at position 15.

虽然当我在 http://jsonpath.com 检查时，我能够检索值。 SQL Server 2016 是否使用一些不同的语法来访问 JSON 值？

原文由 UVData 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

从 phoneNumbers 获取所有信息：

 DECLARE @json nvarchar(max)=
    '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }'

    SELECT [Type], [Number]
    FROM OPENJSON( @json, '$.phoneNumbers' )
    WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');

原文由 Igor Micev 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

SQL Server 2016 支持 JSON。它非常相似，几乎完全相同。你会做你自己的比较。

您不需要使用临时变量 @Table 然后进行操作…

只需运行以下查询

SELECT  JSON_VALUE( JsonData, '$.phoneNumbers[0].type' ) AS [PhoneType],
        JSON_VALUE( JsonData, '$.phoneNumbers[0].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--iPhone 0123-4567-8888

SELECT  JSON_VALUE( JsonData, '$.phoneNumbers[1].type' ) AS [PhoneType],
        JSON_VALUE( JsonData, '$.phoneNumbers[1].number' ) AS [PhoneNumber]
FROM JsonData
WHERE ISJSON( JsonData ) > 0;
--home  0123-4567-8910

查看这些来自 Microsoft 的官方链接，了解更多关于 JSON 支持的信息：

https://msdn.microsoft.com/en-us/library/dn921897.aspx

https://msdn.microsoft.com/en-us/library/dn921898.aspx

原文由 Igor Micev 发布，翻译遵循 CC BY-SA 3.0 许可协议