Select value from JSON block containnig the most recent date

Dennis_Gee · August 17, 2024, 10:36pm

I know… confusing title but the best I could come up with!

I need to be able to select the value of the field “amount” in an output from an API that has a number of blocks of JSON (example has three but could be more) where there is a date value. I want the amount from the block with the most recent start date.

Here’s the JSON:

[
    {
        "id": 5393154049,
        "lastModifiedBy": "",
        "lastModifiedDateTime": "2024-08-01T07:50:35.000Z",
        "createdBy": "System",
        "createdDateTime": "2024-07-14T14:15:50.000Z",
        "glAccountID": 1561559057,
        "leaseID": 2738388993,
        "referenceNumber": "",
        "amount": 1200.0,
        "frequency": "MONTHLY",
        "startDate": "2024-08-01",
        "endDate": null,
        "dueDay": 1,
        "lastPostDate": "2024-08-01",
        "description": ""
    },
    {
        "id": 3428450330,
        "lastModifiedBy": "",
        "lastModifiedDateTime": "2024-07-15T09:58:38.000Z",
        "createdBy": "System",
        "createdDateTime": "2021-09-01T16:23:03.000Z",
        "glAccountID": 1561559057,
        "leaseID": 2738388993,
        "referenceNumber": "",
        "amount": 850.0,
        "frequency": "MONTHLY",
        "startDate": "2021-09-01",
        "endDate": "2024-07-31",
        "dueDay": 1,
        "lastPostDate": "2024-07-01",
        "description": ""
    },
    {
        "id": 2736619520,
        "lastModifiedBy": "",
        "lastModifiedDateTime": "2024-07-15T09:58:38.000Z",
        "createdBy": "System",
        "createdDateTime": "2021-09-01T16:22:32.000Z",
        "glAccountID": 1561559057,
        "leaseID": 2738388993,
        "referenceNumber": "",
        "amount": 692.0,
        "frequency": "MONTHLY",
        "startDate": "2020-08-01",
        "endDate": "2021-08-31",
        "dueDay": 1,
        "lastPostDate": "2021-09-01",
        "description": ""
    }
]

I want to extract the value “1200.0” - i.e. the amount which came from the block where the “startDate” was “2024-08-01”

Anyone feel like geeking out on this one? It’s beyond me…