Trouble parsing json with jsonGetElement

[jbmia]

Hi Folks,

New here and new to FM, but I'm making some good headway.. I've made my way into accessing stock quotes via one of the brokers API's and been successful parsing out the response for a straight stock quote.  I'm now on to options quotes, which has a more complex json response... I've tried using generator and generator seems to stuck as well with a "?" in the field mapping for a key portion of the response.  Here's the complete body:

{
  "underlying": {
    "symbol": "KO",
    "bidSize": 700,
    "bid": 52.5,
    "close": 54.84,
    "mark": 52.51,
    "quoteTime": 1609787762126,
    "fiftyTwoWeekLow": 36.27,
    "description": "Coca-Cola Company (The) Common Stock",
    "change": -2.34,
    "percentChange": -4.26,
    "last": 52.51,
    "markPercentChange": -4.26,
    "markChange": -2.34,
    "ask": 52.51,
    "tradeTime": 1609787762126,
    "askSize": 400,
    "highPrice": 54.63,
    "totalVolume": 15968734,
    "lowPrice": 52.03,
    "openPrice": 54.27,
    "exchangeName": "NYS",
    "fiftyTwoWeekHigh": 60.13,
    "delayed": true
  },
  "isDelayed": true,
  "isIndex": false,
  "volatility": 29,
  "daysToExpiration": 0,
  "symbol": "KO",
  "interval": 0,
  "status": "SUCCESS",
  "strategy": "SINGLE",
  "interestRate": 0.1,
  "underlyingPrice": 52.474999999999994,
  "numberOfContracts": 1,
  "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {
          "symbol": "KO_021921P52.5",
          "multiplier": 100,
          "bid": 1.84,
          "openInterest": 4683,
          "bidSize": 175,
          "highPrice": 2.07,
          "totalVolume": 208,
          "delta": -0.479,
          "rho": -0.033,
          "theoreticalVolatility": 29,
          "putCall": "PUT",
          "volatility": 25.33,
          "description": "KO Feb 19 2021 52.5 Put",
          "quoteTimeInLong": 1609788662180,
          "askSize": 37,
          "lastSize": 0,
          "exchangeName": "OPR",
          "mark": 1.88,
          "inTheMoney": true,
          "tradeDate": null,
          "ask": 1.91,
          "last": 1.87,
          "closePrice": 0.93,
          "settlementType": " ",
          "bidAskSize": "175X37",
          "netChange": 0.94,
          "lowPrice": 0.97,
          "openPrice": 0,
          "tradeTimeInLong": 1609787638288,
          "nonStandard": false,
          "gamma": 0.084,
          "timeValue": 1.85,
          "expirationType": "R",
          "deliverableNote": "",
          "theta": -0.02,
          "vega": 0.075,
          "theoreticalOptionValue": 1.875,
          "strikePrice": 52.5,
          "optionDeliverablesList": null,
          "expirationDate": 1613768400000,
          "daysToExpiration": 46,
          "lastTradingDay": 1613782800000,
          "isIndexOption": null,
          "percentChange": 102.16,
          "markChange": 0.95,
          "markPercentChange": 102.7,
          "mini": false
        }
      ]
    }
  },
  "callExpDateMap": {}
}

I'm only looking to retrieve the "mark" value at this point.. in this case "1.88".   Here's my attempt to retrieve the element:

JSONGetElement( $json ; "$instrument&ExpDateMap.&$expiration&:46.&$strike&.mark" )

Alas, I'm unable to retrieve the "mark" value to the variable successfully using this syntax.  It seems that the issue lies in this section of the response:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {

To complicate things there are several variables here that need to be dealt with... An expiration date:  "2021-02-19" and a price: "52.5".  I'm not sure what the ":46" is since it's a future data.. (??), so I'm just inserting that into the get element request as is for testing purposes.  I don't get an error when evaluating this via script debugger and data viewer, but I don't get a result either...  As I said, Generator can't seem to parse out the fields either... no elements below the section above are being parsed in Generator. 

JSON PATH				VALUE
putExpDateMap.2021-02-19:46.52.5         ?

I'm stumped.. much appreciation if anyone can point me in the right direction.

John

[Agnes Riley]

I believe it should be:

JSONGetElement( $json ; 
["putExpDateMap.mark"];
["putExpDateMap.expiration"];
["putExpDateMap.strike"]
)

 

[Ocean West]

You're going to have a hard time with this because keys have a period in the json object "52.5" fmp can't handle json key's with periods. 

[jbmia]

Thanks Agnes.

I think I was a bit unclear above.  In my attempt above: 

JSONGetElement( $json ; "$instrument&ExpDateMap.&$expiration&:46.&$strike&.mark" )

I neglected to detail out the variables I'm inserting into the request.  These are:

$instrument = call or put ("put" in this case..)

$expiration = expiration date ("2021-02-19" in this case)

$strike = strike price ("52.5")

These are used in an attempt to traverse down through:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {

To get to:

 "mark": 1.88,

  I don't think what you've suggested will work.  Could you take another look?

 

Thanks!

 

John

[jbmia]

Ocean West,

Thanks for the feedback...  even though the "52.5" is a variable and inserted based on the set variable, "$strike"?  

 

John

 

Edited January 4, 2021 by jbmia

[Ocean West]

i think ":" and "." are illegal in json paths. 

 

[Agnes Riley]

I thought you are getting info from JSON, not setting it.

Sorry, I totally missed that your key contains a period.

[jbmia]

Haha... I am getting from JSON.. I'm just trying to get the element "mark" from the response above...  My issue is how to get down through this piece to "mark" to retrieve it:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {
		...
		"mark": 1.88,

It seems this structure is composed of variables  and might look something like this...   "putExpDateMap.$date.$strike.mark, where,

$date = 2021-02-19

$strike = 52.5

I did find a custom function, Parsing JSON with challenging "dot" keys (claris.com), that I'll give a try as a work around for the "dot" in 52.5

I appreciate any further insight anyone might have... 

Thanks again!

John

 

 

[Ocean West]

Pretty ugly but...

Let ( [


a = $JSON ;
b = JSONGetElement ( a ; "putExpDateMap" ) ;
c = JSONListKeys ( b ; "" ) ;
d = Position ( a ; c ; 1 ; 1 ) ;
e = Replace ( a ; d ; Length(c) ; "MYKEY" ) ;
f = JSONGetElement ( e ; "putExpDateMap.MYKEY" ) ;
g = JSONListKeys ( f ; "" ) ;
h = Position ( f ; g ; 1 ; 1 ) ;
i = Replace ( f ; h ; Length(g) ; "MYKEY" ) ;

z = "" 

];

JSONGetElement ( i ; "MYKEY[0].mark" )

)

 

[jbmia]

Looks a lot better than I could come up with... i'm a newb w/ scripting but can see where you're going with it...  Many thanks!

Here's what I get when I attempt to run the script: 

 ? * Line 1, Column 1 Syntax error: value, object or array expected.

I've attached a copy of the script itself in hopes there's something obvious I've done wrong...   the json response detailed above is the result of the "Insert from URL" step below.

Many thanks again for any assistance!!

John

 

Edited January 4, 2021 by jbmia

[Agnes Riley]

I would put that JSOn in a global variable ($$JSON) and use the DataViewer to play with it until you get it right. Break down each row and see what it gives back.

[comment]

First of all, there is nothing wrong with your JSON. The problem is with the library Claris are using to parse the JSON, or with their implementation of it.

Now, if you know in advance the path to the value you want, you could do simply something like:

Let ([
myJSON = Substitute ( $json ; "\"52.5\"" ; "\"52§5\"" ) ;
myKey = "putExpDateMap.2021-02-19:46.52§5[0].mark"
] ;
JSONGetElement ( myJSON ; myKey )
)

If you are assembling the path from variables, take care to get the exact path as shown above. Note that the value of the "52.5" key is an array; at least in theory it could contain more than one object, and thus more than one "mark" value.

 

[jbmia]

Circling back around on this, I wanted to thank everyone who's contributed so far.  It appears I still have some work to do on this one... I'm thinking perhaps I might look for an API that's a bit easier to navigate rather than jump through the hoops mentioned here.   In the meantime huge thanks to you guys for taking time out of your day to respond to my questions.  😉

[comment]

On 1/6/2021 at 4:00 PM, jbmia said:

It appears I still have some work to do on this one...

Doesn't my answer work for you?

 

[jbmia]

Thanks for the follow up...  the challenge I'm having is that the path is dynamic and will need to be navigated based on submitting each variable:

putExpDateMap.2021-02-19:46.52§5[

• 2021-02-19 is the "expiration date", no issue building this.
• ":46" , I believe that this variable is the number of days between today and the expiration date... that shouldn't be too hard to calculate and insert into the request. 😉 
• Then there's the "52.5" which is the strike price..the only issue here being that "dot".  Your solution looks like it would work, but I'd need to break down the price variable into the integer portion, "52" and the decimal portion, the ".5" in the case.. The integer portion could be 1, 2, 3, or even 4 digits in length and the decimal portion may be 1 or 2 or maybe just a "0" value.  I'd then need to concatenate the integer portion, your section symbol, and the decimal portion based on the current requested symbol strike in order to execute your command...

Any further tips would be appreciated..  I'm still new to this ...

John 

[comment]

Suppose you have already defined these variables:

$instrument = "put" ;
$expiration = "2021-02-19" ;
$days = 46 ;
$strike = 52.5 ;

then you can modify the above calculation to:

Let ([
newStrike = Substitute ( $strike ; "." ; "§" ) ;
myJSON = Substitute ( $json ; Quote ( $strike ) ; Quote ( newStrike ) ) ;
myKey = $instrument & "ExpDateMap." & $expiration & ":" & $days & "." & newStrike & "[0].mark"
] ;
JSONGetElement ( myJSON ; myKey )
)

Demo file attached.

 

DEMOParseJSONwDot.fmp12

Edited January 8, 2021 by comment

[Ocean West]

perhaps use a web viewer with some javascript that uses a different JSON parsing library that can manage paths with characters that FileMaker can't.

[comment]

I have modified the above calculation further to remove any hard-coded values, and added a demo file.

[jbmia]

Wow.  You guys are are awesome!  Thanks comment for the demo file!  I'm going to get on this today.

John

[jbmia]

Comment,

Your are the man!  I was able to get this working using your example.  Many thanks for going out of your way to solve this problem and build the demo file.  It was very helpful  Thank you so much!  

There were a couple additional wrinkles I had to solve: (1)  data provider wants a whole number strike price as a single decimal (e.g., $65 must be 65.0) so I found a custom function, "NumberFormat", and converted the strike price before setting the $strike variable.  (2) the instrument had to be sent as an upper (CALL, PUT), yet the JSON response is lower (call, put), so set variables for each.  Upper for the request, and lower for parsing the response.

In the interest of sharing (I couldn't find another parse example like this...), given those who have shared with me, 🙂, following is the complete script for reference purposes.  No doubt there could be improvements, but it does work (at least based on preliminary tests).  Feel free to suggest improvements...

Thanks again to everyone who jumped in to assist!

John

#Options Prices: updateMarketPrice[Options Prices]
#SCRIPT: updateMarketPrice[Options Prices] used to retrieve options chain and parse quotes and other data into Option Prices table.

#____________________________________________________________
#Set variables for underlying symbol, instrument (e.g., stock, call, put, etc), strike price, and expiration date
Set Variable [ $underlying; Value:Options Prices::underlying ]
Set Variable [ $upperInstrument; Value:Options Prices::upperInstrument ]
Set Variable [ $lowerInstrument; Value:Options Prices::lowerInstrument ]
Set Variable [ $strike; Value:Options Prices::DecimalStrike ]
Set Variable [ $expiration; Value:Options Prices::expDateFormatted ]
Set Variable [ $DaysToExpir; Value:Options Prices::Number Days to Expiration ]

#___________________________________________________________
#curl request via Insert From URL command.
Insert from URL [ $json2; "https://api."XXX".com/v1/marketdata/chains? apikey="XXX"&symbol="& $underlying &"&contractType="& $upperInstrument &"&includeQuotes=TRUE&strategy=SINGLE&strike="& $strike &"&fromDate="&$expiration &"&toDate="& $expiration & ""; cURL options: "cURL options: -X GET --header Authorization: --show-error --dump-header $dump" ]
[ Select; No dialog ]

#Switch "dots" for "sections" and save complete JSON response (for testing)
Set Variable [ $newStrike; Value:Substitute ( $strike ; "." ; "§" ) ]
Set Variable [ $myJSON; Value:Substitute ( $json2 ; Quote ( $strike ) ; Quote ( $newStrike ) ) ] 
Set Field [ Options Prices::JSON; $myJSON ]

#____________________________________________________________
#BUILD KEYS TO RETRIEVE VARIABLES
Set Variable [ $myMark; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. mark" ]
Set Variable [ $mySymbol; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. symbol" ]
Set Variable [ $myAsk; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].ask" ]
Set Variable [ $myBid; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].bid" ]
Set Variable [ $myLast; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].last" ]

#_____________________________________________________________ 
#RETRIEVE VARIABLES
Set Variable [ $mark; Value:JSONGetElement ( $myJSON ; $myMark ) ]
Set Variable [ $symb; Value:JSONGetElement ( $myJSON ; $mySymbol) ] 
Set Variable [ $ask; Value:JSONGetElement ( $myJSON ; $myAsk) ]
Set Variable [ $last; Value:JSONGetElement ( $myJSON ; $myLast) ] 
Set Variable [ $bid; Value:JSONGetElement ( $myJSON ; $myBid) ]

#————————————————————————————————————————————————————————— —————
#SET VARIABLES INTO FIELDS
Set Field [ Options Prices::market price; $mark ] 
Set Field [ Options Prices::Symbol.; $symb ] 
Set Field [ Options Prices::Ask; $ask ]
Set Field [ Options Prices::Bid; $bid ]
Set Field [ Options Prices::Last; $last ]

 

Edited January 9, 2021 by jbmia

[comment]

3 hours ago, jbmia said:

No doubt there could be improvements

There are couple of ways this could be streamlined. Some of these are cosmetic, but one practically screams off the page:

All of your values are retrieved from the same branch of the JSON. Instead of repeatedly parsing the entire tree for each individual value, you should isolate the target branch in a variable once, then parse out the individual values from this variable.

IOW, instead of:

#____________________________________________________________
#BUILD KEYS TO RETRIEVE VARIABLES
Set Variable [ $myMark; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. mark" ]
Set Variable [ $mySymbol; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. symbol" ]
Set Variable [ $myAsk; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].ask" ]
Set Variable [ $myBid; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].bid" ]
Set Variable [ $myLast; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].last" ]

#_____________________________________________________________ 
#RETRIEVE VARIABLES
Set Variable [ $mark; Value:JSONGetElement ( $myJSON ; $myMark ) ]
Set Variable [ $symb; Value:JSONGetElement ( $myJSON ; $mySymbol) ] 
Set Variable [ $ask; Value:JSONGetElement ( $myJSON ; $myAsk) ]
Set Variable [ $last; Value:JSONGetElement ( $myJSON ; $myLast) ] 
Set Variable [ $bid; Value:JSONGetElement ( $myJSON ; $myBid) ]

#————————————————————————————————————————————————————————— —————
#SET VARIABLES INTO FIELDS
Set Field [ Options Prices::market price; $mark ] 
Set Field [ Options Prices::Symbol.; $symb ] 
Set Field [ Options Prices::Ask; $ask ]
Set Field [ Options Prices::Bid; $bid ]
Set Field [ Options Prices::Last; $last ]

you would have something like (untested):

Set Variable [ $myKey; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]" ]
Set Variable [ $myBranch; Value:JSONGetElement ( $myJSON ; $myKey ) ]

#————————————————————————————————————————————————————————— —————
#POPULATE FIELDS
Set Field [ Options Prices::market price; JSONGetElement ( $myBranch ; "mark" ) ] 
Set Field [ Options Prices::Symbol.; JSONGetElement ( $myBranch ; "symbol" ) ] 
Set Field [ Options Prices::Ask; JSONGetElement ( $myBranch ; "ask" ) ] 
Set Field [ Options Prices::Bid; JSONGetElement ( $myBranch ; "bid" ) ] 
Set Field [ Options Prices::Last; JSONGetElement ( $myBranch ; "last" ) ] 

 

 

Edited January 9, 2021 by comment

[jbmia]

Thanks !!   Will update on my side.  Again, much appreciation for the assistance.