Question 109274 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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
  .
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search