Given mulitple values for each stopno field, grab the newest record for each unique stop number

[David_Hernandez (DH)]

I want to grab each jarray json for each unique stopnumber, that was last inserted.

For example:

[StopNumber = 1, Inserted 11-17],
[StopNumber = 2, Inserted 11-17],
[StopNumber = 1, Inserted 11-11]

The Jarray I would want returned is

[StopNumber = 1, Inserted 11-17],
[StopNumber = 2, Inserted 11-17]

This is the code I have that doesn’t work:

new JArray(
    R_Content_Fixed
        .Where(doc =>
        {
            var stopNoField = doc["Fields"].FirstOrDefault(field => field["Name"].Value<string>() == "STOPNO");
            return stopNoField != null && !string.IsNullOrEmpty(stopNoField["Value"].Value<string>());
        })
        .GroupBy(doc => doc["Fields"].First(field => field["Name"].Value<string>() == "STOPNO")["Value"].Value<string>())
        .Select(group => group.OrderByDescending(doc => doc["InsertDateTime"].Value<DateTime>()).First())
)

Part of my Jarray:

[
{
“Bytes”: “”,
“DocumentId”: “RTMS20-B800015740-1”,
“DocClassName”: “RTMS20”,
“InsertDateTime”: “2023-10-11T09:43:22”,
“ImportDateTime”: “2023-10-11T09:43:22”,
“FileType”: “PDF”,
“Annotations”: ,
“Fields”: [
{
“Name”: “ORDERNO”,
“Value”: “12141902”
},
{
“Name”: “BILLTOID”,
“Value”: “12141902”
},
{
“Name”: “BOLNO”,
“Value”: “”
},
{
“Name”: “DOCTYPE”,
“Value”: “EBOL”
},
{
“Name”: “INVNO”,
“Value”: “”
},
{
“Name”: “BATCHID”,
“Value”: “12141902_61164208_5”
},
{
“Name”: “TERMINAL”,
“Value”: “N/A”
},
{
“Name”: “STOPNO”,
“Value”: “61164208”
},
{
“Name”: “REFNUM”,
“Value”: “12141902”
}
]
},
{
“Bytes”: “1”,
“DocumentId”: “RTMS20-B800015760-1”,
“DocClassName”: “RTMS20”,
“InsertDateTime”: “2023-10-11T10:06:34”,
“ImportDateTime”: “2023-10-11T10:06:34”,
“FileType”: “PDF”,
“Annotations”: ,
“Fields”: [
{
“Name”: “ORDERNO”,
“Value”: “12141902”
},
{
“Name”: “BILLTOID”,
“Value”: “12141902”
},
{
“Name”: “BOLNO”,
“Value”: “”
},
{
“Name”: “DOCTYPE”,
“Value”: “EBOL”
},
{
“Name”: “INVNO”,
“Value”: “”
},
{
“Name”: “BATCHID”,
“Value”: “12141902_61164208_6”
},
{
“Name”: “TERMINAL”,
“Value”: “N/A”
},
{
“Name”: “STOPNO”,
“Value”: “61164208”
},
{
“Name”: “REFNUM”,
“Value”: “12141903”
}
]
},
{
“Bytes”: “3”,
“DocumentId”: “RTMS20-B800015780-1”,
“DocClassName”: “RTMS20”,
“InsertDateTime”: “2023-10-17T13:26:21”,
“ImportDateTime”: “2023-10-17T13:26:21”,
“FileType”: “PDF”,
“Annotations”: ,
“Fields”: [
{
“Name”: “ORDERNO”,
“Value”: “12141902”
},
{
“Name”: “BILLTOID”,
“Value”: “12141902”
},
{
“Name”: “BOLNO”,
“Value”: “”
},
{
“Name”: “DOCTYPE”,
“Value”: “EBOL”
},
{
“Name”: “INVNO”,
“Value”: “”
},
{
“Name”: “BATCHID”,
“Value”: “12141902_61164208_13”
},
{
“Name”: “TERMINAL”,
“Value”: “”
},
{
“Name”: “STOPNO”,
“Value”: “61164210”
},
{
“Name”: “REFNUM”,
“Value”: “12141902”
}
]
}}

[ppr (Peter Preuss)]

We do see a discrepancy between the case description samples and sample JSON.

Maybe you can redefine it or define the exact output related to the shared JSON sample

[David_Hernandez (DH)]

The JSON value would return the 2nd and 3rd value (There are a lot more in the actual json, but keeping it short for forums sake).

The reason being is because the 1st and 2nd array Jtokens contain the same stop number, 61164208, but the second one was inserted the most recent. So we are grabbing the second one.

The third one is the only one with the StopNo 6114210, so we grab that one.

[ppr (Peter Preuss)]

Working with Tuples can help to shorten it

done in VB.Net

Flow:

ResultTuples =

(From x In myJArray
From s In x("Fields").Where(Function (f) f("Name").tostring.equals("STOPNO"))
Select t = Tuple.Create(s("Value").toString().Trim(),x("InsertDateTime").Value(Of DateTime))
Group t By t.Item1 Into grp=Group
Let m = grp.OrderBy(Function (o) o.Item2).Last()
Select v=m).ToArray

Result:

We can use it and Create other formats / outputs from it e.g.

Kindly note: we fixed the invalid JSON snippet by setting “” to the Annotations properties as Value

[ppr (Peter Preuss)]

Just to add a few variations on the reformats

With a adaption of the LINQ we can also take some controls on it