How do you use SQL Server to convert json to a flat file (table) format?

Question

I am using option chain output from TD Ameritrade and would like to access various values such as bid, ask, etc. and the symbol and price from underlying header.

My problem seems to be the date and strike price are keys and not fixed.

The JSON format looks as follows:

(I couldn't post my question the json text looked like code... not sure how to post that)

The output I desire should look like this:

symbol   underlyingprice    putCall   expdate       strike    bid   ask
-------------------------------------------------------------------------
AMZN     90.965             PUT       2023-03-17:6  90.0      1.83  1.88
AMZN     90.965             PUT       2023-03-17:6  91.0      2.27  2.36
  .

Answer 1

In the interests of brevity the JSON in the following code is a much reduced example of the JSON presented in a previous Stack Overflow question, Parse Nested JSON Data of Share Market Data, which also seems to be using data returned from Ameritrade's Get Open Chain method:

declare @json nvarchar(max) = N'{
    "symbol": "AAPL",
    "underlyingPrice": 132.325,
    "putExpDateMap": {
        "2021-01-08:8": {
            "132.0": [
                {
                    "putCall": "PUT",
                    "bid": 2.4,
                    "ask": 2.43,
                    "strikePrice": 132.0
                }
            ],
            "133.0": [
                {
                    "putCall": "PUT",
                    "bid": 2.93,
                    "ask": 2.95,
                    "strikePrice": 133.0
                }
            ]
        }
    }
}';

with putExpDateMapKeys (putExpDate, jsonPath) as (
  select
    [key],
    N'$.putExpDateMap.' + quotename([key], N'"') -- e.g.: '$.putExpDateMap."2021-01-08:8"'
  from openjson(@json, N'$.putExpDateMap')
), strikePriceKeys (putExpDate, jsonPath) as (
  select
    putExpDate,
    N'$.' + quotename(putExpDate, N'"') + N'.' + quotename([key], N'"') -- e.g.: '$."2021-01-08:8"."132.0"'
  from putExpDateMapKeys
  cross apply openjson(@json, jsonPath)
)
select J.symbol, J.underlyingPrice, S.putCall, putExpDate as expdate, S.strikePrice, S.bid, S.ask
from openjson(@json) with (
  symbol nvarchar(4),
  underlyingPrice float,
  putExpDateMap nvarchar(max) as JSON
) J
cross apply strikePriceKeys SPK
outer apply openjson(putExpDateMap, SPK.jsonPath) with (
  putCall nvarchar(4),
  strikePrice float,
  bid float,
  ask float
) S;

Which yields the output:

symbol	underlyingPrice	putCall	expdate	strikePrice	bid	ask
AAPL	132.325	PUT	2021-01-08:8	132	2.4	2.43
AAPL	132.325	PUT	2021-01-08:8	133	2.93	2.95

So how does this query work? The property keys inside putExpDateMap are dynamic so we cannot use a hardcoded JSON path to access their contents. I use two CTE expressions to enumerate the keys:

1. putExpDateMapKeys enumerates the date keys, e.g.: "2021-01-08:8"
2. within each date key (since I assume there could be more than one) strikePriceKeys enumerates the strike price keys, e.g.: "132.0" and "133.0"

Each CTE expression also returns a JSON path which then allows the final invocation of openjson() to access the putCall, bid, ask and strikePrice values.

Answer 2

An alternative version to Always Learning's great answer which doesn't use dynamic JSON_VALUE which isn't supported in 2016:

declare @json nvarchar(max) = N'{
    "symbol": "AAPL",
    "underlyingPrice": 132.325,
    "putExpDateMap": {
        "2021-01-08:8": {
            "132.0": [
                {
                    "putCall": "PUT",
                    "bid": 2.4,
                    "ask": 2.43,
                    "strikePrice": 132.0
                }
            ],
            "133.0": [
                {
                    "putCall": "PUT",
                    "bid": 2.93,
                    "ask": 2.95,
                    "strikePrice": 133.0
                }
            ]
        }
    }
}';
select JSON_VALUE(jj, '$.symbol') as symbol
  , JSON_VALUE(jj,'$.underlyingPrice') AS underlyingPrice
  , n.[key] AS date
  , nn.[key] AS strikePrice
  , JSON_VALUE(nn.value, '$[0].putCall') AS pCall
  , JSON_VALUE(nn.value, '$[0].bid') AS bid
  , JSON_VALUE(nn.value, '$[0].ask') AS ask
  , JSON_VALUE(nn.value, '$[0].strikePrice') AS strike
  , nn.value
FROM (
     select @json AS jj
  ) j
CROSS APPLY OPENJSON(jj, '$.putExpDateMap') n
CROSS APPLY OPENJSON(n.value) nn

Here, i'm just shredding every array key and then shredding it's content further down to get the values. I use one assumption, that strikePrice array only contains one row. If not, you can do another level of OPENJSON to get every other row