SQL OPENJSON不返回子数组的值。

Question

我有一个根据Microsoft函数正确格式化的JSON文件。但是，它拒绝从嵌套数组返回值。

以下是JSON文件的摘录。

我想返回以下字段: id、符号、名称和价格。我可以得到前三个，但是价格总是在SQL查询结果中显示为null。

JSON文件片段：

    {
     "status": {
       "timestamp": "2021-01-06T07:14:42.132Z",
       "error_code": 0,
       "error_message": null,
       "elapsed": 14,
       "credit_count": 1,
       "notice": null,
       "total_count": 4180
     },
     "data": [
       {
         "id": 1,
         "name": "Bitcoin",
         "symbol": "BTC",
         "slug": "bitcoin",
         "num_market_pairs": 9772,
         "date_added": "2013-04-28T00:00:00.000Z",
         "tags": [
           "mineable",
           "pow",
           "sha-256",
           "store-of-value",
           "state-channels"
         ],
         "max_supply": 21000000,
         "circulating_supply": 18592156,
         "total_supply": 18592156,
         "platform": null,
         "cmc_rank": 1,
         "last_updated": "2021-01-06T07:13:02.000Z",
         "quote": {
           "USD": {
             "price": 36248.609255662224,
             "volume_24h": 225452557837159.16,
             "percent_change_1h": 2.74047145,
             "percent_change_24h": 19.54362963,
             "percent_change_7d": 29.31750604,
             "market_cap": 673939798064.3159,
             "last_updated": "2021-01-06T07:13:02.000Z"
           }
         }
       }

下面是我使用的SQL查询：

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\TSP\output.json', SINGLE_CLOB) AS j

Select iif(ISJSON(@JSON)=1,'YES','NO') JSON_OK


Select * FROM OPENJSON (@JSON, '$.data')
    WITH (
        id              int 
       ,symbol          varchar(20) 
       ,[name]          varchar(50)
      ,price            float '$.data.quote.USD[0]'
    )

我已经尽我所能让价格出现，但我错过了一些东西，因为它不合作。此外，我将数据库兼容性级别设置为130，因为我读到这可能是问题所在.还是没有运气。

任何帮助都将不胜感激。

Answer 1

$.data.quote.USD不是数组，而是一组属性。它也已经在$.data上下文中了，所以不应该在其路径中包括data。相反，请尝试以下几点：

select *
from openjson(@JSON, '$.data') with
(
    id      int
    ,symbol varchar(20)
    ,[name] varchar(50)
    ,price  float '$.quote.USD.price'
)