Retrieve value of one item by searching and sorting two other items in JSON

[Dennis_Gee]

Hi there,

I have an API output that contains data in multiple collections. I need the value of one item, which comes from a collection containing another item and from the collection that contains the most recent date value. This is similar to another post I made where @Msquare_Automation really helped me out, but introduces another level which is beyond my skills unfortunately… ( Select value from JSON block containing the most recent date - How To - Make Community)

The example API output below shows three collections which is the result of an API GET.

My objective is to retrieve the value of the field “id” where “description = RPA”, and “createdDateTime” is the most recent. In the example there are two collections containing “description” = “RPA”, but I want to get “6526271503” - i.e. from the most recent collection

[
    {
        "statusCode": 200,
        "headers": [
            {
                "name": "date",
                "value": "Tue, 01 Oct 2024 04:23:33 GMT"
            },
            {
                "name": "x-total-count",
                "value": "3"
            },
            {
                "name": "set-cookie",
                "value": "JSESSIONID=171F2FC20D6F1F2E79E2D32788D440D5; Path=/pd; HttpOnly;HttpOnly"
            },
            {
                "name": "keep-alive",
                "value": "timeout=3, max=200"
            },
            {
                "name": "connection",
                "value": "Keep-Alive"
            },
            {
                "name": "content-type",
                "value": "application/json"
            },
            {
                "name": "transfer-encoding",
                "value": "chunked"
            }
        ],
        "cookieHeaders": [
            "JSESSIONID=171F2FC20D6F1F2E79E2D32788D440D5; Path=/pd; HttpOnly;HttpOnly",
            "BIGipServerPRD-API-HTTP=489824522.20480.0000; path=/; Httponly",
            "TS011a8d61=01c7a403d72c1a37574c5f3bad82ed6df0d8f89580e9f9d0920e0208c94effa7459b87e95d512abebfa6a7cd0dd3cec4d76d1f378bd299289f4713e3f13b5f5b454cb818b1; Path=/; Domain=.api.acme.com",
            "TS01ae6105=01c7a403d771fb076dab032940132eceeb0740164de9f9d0920e0208c94effa7459b87e95d47399900c19dbc3258be959d6059d4bedad7b833174457a2ba0b101e81bd9bb9; path=/pd"
        ],
        "data": [
            {
                "id": 6503006855,
                "lastModifiedBy": "d@mykc.r",
                "lastModifiedDateTime": "2024-09-30T20:00:37.000Z",
                "createdBy": "mapi",
                "createdDateTime": "2023-09-21T18:02:34.000Z",
                "fileType": "text/text",
                "fileName": null,
                "description": "",
                "publishToTenantPortal": false,
                "publishToOwnerPortal": false,
                "entityType": "BUILDING",
                "entityId": 2761129985
            },
            {
                "id": 6506610690,
                "lastModifiedBy": "d@mykc.r",
                "lastModifiedDateTime": "2024-09-30T20:00:37.000Z",
                "createdBy": "mapi",
                "createdDateTime": "2023-09-23T15:28:28.000Z",
                "fileType": "application/pdf",
                "fileName": null,
                "description": "RPA",
                "publishToTenantPortal": true,
                "publishToOwnerPortal": true,
                "entityType": "BUILDING",
                "entityId": 2761129985
            },
            {
                "id": 6526271503,
                "lastModifiedBy": "d@mykc.r",
                "lastModifiedDateTime": "2024-09-30T20:00:37.000Z",
                "createdBy": "mapi",
                "createdDateTime": "2023-10-01T14:53:38.000Z",
                "fileType": "application/pdf",
                "fileName": null,
                "description": "RPA",
                "publishToTenantPortal": false,
                "publishToOwnerPortal": true,
                "entityType": "BUILDING",
                "entityId": 2761129985
            }
        ],
        "fileSize": 979
    }
]

[Msquare_Automation]

Hi @Dennis_Gee
Sample workflow :

We have created a sample json structure from the output bundle you have shared using parse json module:

With the iterator module you can sot each data in array based on the created date:

Adding a filter to take only the data with description “RPA”:

Array aggregate:

And taking the first id from the sorted array:

Output:

Best regards,

Msquare Automation
Gold Partner of Make
@Msquare_Automation

[Msquare_Automation]

Hi @Dennis_Gee
You can also do it in one single operation as follows:

{{first(map(sort(417.data; "desc"; "createdDateTime"); "id"; "description"; "RPA"))}}

Output:

Best regards,

Msquare Automation
Gold Partner of Make
@Msquare_Automation

[Dennis_Gee]

Amazing - thank you @Msquare_Automation !