json - SQL OPENJSON is not returning the values from sub-arrays

Question

I have a JSON file that is properly formatted according to the Microsoft ISJSON function. However, it refuses to return a value from the nested array.

Here is an excerpt from the JSON file.
I want to return the following fields: id, symbol, name, and price. I can get the first three, but the price always shows up null in the SQL query results.

JSON FILE SNIPPET:

    {
     "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"
           }
         }
       }

Here is the SQL Query that I'm using:

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:TSPoutput.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]'
    )

I've tried everything I can think of to get the price to appear, but I'm missing something as it's not cooperating. Also, I set the database compatibility level to 130 as I read that could be the problem.... Still no luck.

Any help would be much appreciated.

Answer 1

$.data.quote.USD is not an array, it's a set of properties. It's also already inside the $.data context so should not include data in its path. Try the following instead:

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