0

Reading JSON list

Hi, how can a list of JSON objects be read like all the values nthe "Monthly Adjusted Time Series":

{
"Meta Data": {
"1. Information": "Monthly Adjusted Prices and Volumes",
"2. Symbol": "MSFT",
"3. Last Refreshed": "2018-11-30",
"4. Time Zone": "US/Eastern"
},
"Monthly Adjusted Time Series": {
"2018-11-30": {
"1. open": "107.0500",
"2. high": "112.2400",
"3. low": "99.3528",
"4. close": "110.8900",
"5. adjusted close": "110.8900",
"6. volume": "720204486",
"7. dividend amount": "0.4600"
},
"2018-10-31": {
"1. open": "114.7500",
"2. high": "116.1800",
"3. low": "100.1100",
"4. close": "106.8100",
"5. adjusted close": "106.3440",
"6. volume": "927547942",
"7. dividend amount": "0.0000"
},
"2018-09-28": {
"1. open": "110.8500",
"2. high": "115.2900",
"3. low": "107.2300",
"4. close": "114.3700",
"5. adjusted close": "113.8710",
"6. volume": "480255674",
"7. dividend amount": "0.0000"
}, etc.

This is not an array and the name changes according the date. Reading the name or raplacing the name by a variable could help. However, neither seems to be possible.

6 replies

null
    • Gunther
    • 5 yrs ago
    • Reported - view

    Hi, first put all data into a variable. Then you can adress the data :

    let data := {
    'Meta Data': {
    '1. Information': "Monthly Adjusted Prices and Volumes",
    '2. Symbol': "MSFT",
    '3. Last Refreshed': "2018-11-30",
    '4. Time Zone': "US/Eastern"
    },
    'Monthly Adjusted Time Series': {
    '2018-11-30': {
    '1. open': "107.0500",
    '2. high': "112.2400",
    '3. low': "99.3528",
    '4. close': "110.8900",
    '5. adjusted close': "110.8900",
    '6. volume': "720204486",
    '7. dividend amount': "0.4600"
    }
    }
    };
    alert(data.'Monthly Adjusted Time Series'.'2018-11-30'.'1. open');  << gives the open-data from 2018-11-30

    • heinzflamm
    • 5 yrs ago
    • Reported - view

    Thanks, Günther, I got that far.

    The problem occurs when the date e.g. '2018-11-30' is not inline but a generated variable containing the string '2018-11-30'.

    There are two very basic options: (1) read the date (e.g. '2018-11-30') as string data and filter in the script or (2) generate a variable with the wanted data and put it in the access path of the json data. I couldn't figure out how one of those works. Please be aware that the entire result may contain hundreds of daily entries and it should be possible to read each of them.

    Thanks again for your comment, very appreciated.

    • Sean
    • 5 yrs ago
    • Reported - view

    I don't have access to the response.result and I have never done this before, but I am curious. Would the .fields property work in this situation?

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @sean ... what is the .fields property of which you speak?  Are you refering to the fields property in the Ninox REST/API? 

    fields

    • Sean
    • 4 yrs ago
    • Reported - view

    @Mconneen, It’s been a while, but yes, I believe that’s what I was referring to.

    • Business Analyst
    • Terry_Hopper
    • 8 mths ago
    • Reported - view

    I'm posting this here just in case anyone comes across this old thread looking for a similar solution—like I did. For this example, this formula works:

    item(data.'Monthly Adjusted Time Series', format(yourDate, "YYYY-MM-DD")).'1. open'

    Either create a variable called yourDate to hold the date you need, or replace "yourDate" in this formula with the date you need.

Login to reply

Content aside

  • 8 mths agoLast active
  • 6Replies
  • 2594Views
  • 1 Following