Struggling to flatten document using LINQ map index

[dbblackdiamond]

Hi,

I know this is a very basic question, but I have been wrestling with this for the last few days and I am not sure what I am doing wrong. I have a number of documents with the format below:

{
    "categories": [
        {
            "adminfee": 16,
            "division": "4D",
            "entryfee": 40,
            "name": "Open",
            "splits": ".7s",
            "starttime": "10:00"
        },
        {
            "adminfee": 15,
            "division": "3D",
            "entryfee": 30,
            "name": "Youth",
            "splits": ".7s",
            "starttime": "10:00"
        }
    ],
    "date": "2024-11-28",
    "doubleheader": true,
    "entries": [
        {
            "affiliationid": "",
            "category": "Open",
            "constraint": "",
            "entryfee": 80,
            "entryfeesreceived": true,
            "entryfeessent": true,
            "entryid": "entries/oCdPFeeYQCVkThdREbdphc",
            "horseid": "",
            "horsename": "horse 247",
            "horserank": 0,
            "jackpotid": "jackpots/GyJsuatd7mPtYw2ZCgbdF3",
            "jp1run": true,
            "jp2run": true,
            "multihorses": false,
            "position": 1,
            "riderid": "users/dm3aVPrA6JjoqqUFJaPyZR",
            "ridername": "Awesome Rider 247",
            "roll": false,
            "youthroll": false
        },
        {
            "affiliationid": "",
            "category": "Open",
            "constraint": "",
            "entryfee": 80,
            "entryfeesreceived": true,
            "entryfeessent": true,
            "entryid": "entries/hTbhowWGx4L9AcfD4bYf9J",
            "horseid": "",
            "horsename": "horse 102",
            "horserank": 0,
            "jackpotid": "jackpots/GyJsuatd7mPtYw2ZCgbdF3",
            "jp1run": true,
            "jp2run": false,
            "multihorses": false,
            "position": 2,
            "riderid": "users/C88ht5m623q8YX4TgBptwF",
            "ridername": "Awesome Rider 102",
            "roll": true,
            "youthroll": false
        },
        {
            "affiliationid": "",
            "category": "Open",
            "constraint": "",
            "entryfee": 80,
            "entryfeesreceived": true,
            "entryfeessent": true,
            "entryid": "entries/SqXVEwZnT4zkiMrUCQXzhC",
            "horseid": "",
            "horsename": "horse 38",
            "horserank": 0,
            "jackpotid": "jackpots/GyJsuatd7mPtYw2ZCgbdF3",
            "jp1run": true,
            "jp2run": false,
            "multihorses": false,
            "position": 3,
            "riderid": "users/X8qPhsCXbmXGkcDHhPg5nX",
            "ridername": "Awesome Rider 38",
            "roll": true,
            "youthroll": false
        }
    ],
    "jackpotid": "jackpots/GyJsuatd7mPtYw2ZCgbdF3",
    "jackpotname": "Jackpot Series AvgTime JP1 DH",
    "jackpotnumber": "Jackpot 1",
    "result": {
        "Open": {
            "1D": [
                {
                    "category": "Open",
                    "division": "1D",
                    "finaltime": "15.066",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 58",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "137",
                    "points": 25,
                    "position": 63,
                    "riderid": "users/p5J9zBRZMfGdMACQeKSJ6V",
                    "ridername": "Awesome Rider 58",
                    "runid": "runs/Gxr7kSLQAgvaRxxJxJq6Ei",
                    "runtime": "15.066",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                },
                {
                    "category": "Open",
                    "division": "1D",
                    "finaltime": "15.082",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 79",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "107",
                    "points": 24,
                    "position": 10,
                    "riderid": "users/q8YpW5TPZSHCYQYyr6N8uk",
                    "ridername": "Awesome Rider 79",
                    "runid": "runs/eUHMfhSreytaJx8EHJTXc",
                    "runtime": "15.082",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ],
            "2D": [
                {
                    "category": "Open",
                    "division": "2D",
                    "finaltime": "15.866",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 75",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "137",
                    "points": 25,
                    "position": 6,
                    "riderid": "users/a5S8ELQETNQppahkgCQBYL",
                    "ridername": "Awesome Rider 75",
                    "runid": "runs/rYJGdMJcdw4CCDBsvtN9Jk",
                    "runtime": "15.866",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                },
                {
                    "category": "Open",
                    "division": "2D",
                    "finaltime": "15.873",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 6",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "107",
                    "points": 24,
                    "position": 56,
                    "riderid": "users/RAimwreDGQkB8P4HDENMhH",
                    "ridername": "Awesome Rider 6",
                    "runid": "runs/cExgJyKXgJstRyyF5iDnQ9",
                    "runtime": "15.873",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ],
            "3D": [
                {
                    "category": "Open",
                    "division": "3D",
                    "finaltime": "16.522",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 210",
                    "jackpotnumber": "Jackpot 2",
                    "payout": "137",
                    "points": 25,
                    "position": 12,
                    "riderid": "users/cPjUGcUc3ixmewmj7SGfoe",
                    "ridername": "Awesome Rider 210",
                    "runid": "runs/2a7JS7yBSAabHCqqmZLk6J",
                    "runtime": "16.522",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                },
                {
                    "category": "Open",
                    "division": "3D",
                    "finaltime": "16.574",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 230",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "107",
                    "points": 24,
                    "position": 41,
                    "riderid": "users/kxXWzGmRMA8tqrH7jLDeFZ",
                    "ridername": "Awesome Rider 230",
                    "runid": "runs/HTXW8mGp63W6uJsZmkBKSe",
                    "runtime": "16.574",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ],
            "4D": [
                {
                    "category": "Open",
                    "division": "4D",
                    "finaltime": "17.374",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 238",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "137",
                    "points": 25,
                    "position": 51,
                    "riderid": "users/uS9HSU3uqd7iQpT25Rq4Hi",
                    "ridername": "Awesome Rider 238",
                    "runid": "runs/Q82TiLr9pBu2HZj2WAhhem",
                    "runtime": "17.374",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ]
        },
        "Youth": {
            "1D": [
                {
                    "category": "Youth",
                    "division": "1D",
                    "finaltime": "15.045",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 252",
                    "jackpotnumber": "Jackpot 2",
                    "payout": "117",
                    "points": 25,
                    "position": 21,
                    "riderid": "users/RBVSf3qwGNPNbKcy5cd5C7",
                    "ridername": "Awesome Rider 252",
                    "runid": "runs/G9qpuUVGotKvccFmX9TyYd",
                    "runtime": "15.045",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                },
                {
                    "category": "Youth",
                    "division": "1D",
                    "finaltime": "15.075",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 7",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "102",
                    "points": 24,
                    "position": 34,
                    "riderid": "users/8UKy4E9pLr5u3G5yBVpSK9",
                    "ridername": "Awesome Rider 7",
                    "runid": "runs/25RvH8BraoeaMQd7h74SGR",
                    "runtime": "15.075",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ],
            "2D": [
                {
                    "category": "Youth",
                    "division": "2D",
                    "finaltime": "15.78",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 137",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "117",
                    "points": 25,
                    "position": 22,
                    "riderid": "users/ecC5QXAZLhchxjxNhMQyXL",
                    "ridername": "Awesome Rider 137",
                    "runid": "runs/PKBcSgQbrMC4eBXUFT48Yf",
                    "runtime": "15.78",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                },
                {
                    "category": "Youth",
                    "division": "2D",
                    "finaltime": "15.805",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 246",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "102",
                    "points": 24,
                    "position": 66,
                    "riderid": "users/8GrUwacwgWV2x45Q9nq6m8",
                    "ridername": "Awesome Rider 246",
                    "runid": "runs/FjP8q8EsHSwj7TPaoZknN",
                    "runtime": "15.805",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ],
            "3D": [
                {
                    "category": "Youth",
                    "division": "3D",
                    "finaltime": "16.462",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 214",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "117",
                    "points": 25,
                    "position": 27,
                    "riderid": "users/mYtm5EqBGFZoUBy8uaNJa4",
                    "ridername": "Awesome Rider 214",
                    "runid": "runs/r2h8q4xEK4vG2ipNmcX4sL",
                    "runtime": "16.462",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
                }
            ]
        }
    },
    "resultid": "results/FiYfE54RGairzLdtG6PueL",
    "seriesid": "series/UG9HCSUqYvF95jrdJnmk3",
    "status": "published",
    "@metadata": {
        "@collection": "JackpotResults",
        "Raven-Go-Type": "data.JackpotResult"
    }
}

I m trying to create a map index that is going to extract all the objects of type run out of the result object based on the value of seriesid. Here is an example of such object:

{
                    "category": "Youth",
                    "division": "3D",
                    "finaltime": "16.462",
                    "firstbarreldown": false,
                    "horseid": "",
                    "horsename": "horse 214",
                    "jackpotnumber": "Jackpot 1",
                    "payout": "117",
                    "points": 25,
                    "position": 27,
                    "riderid": "users/mYtm5EqBGFZoUBy8uaNJa4",
                    "ridername": "Awesome Rider 214",
                    "runid": "runs/r2h8q4xEK4vG2ipNmcX4sL",
                    "runtime": "16.462",
                    "scratch": false,
                    "secondbarreldown": false,
                    "thirdbarreldown": false
}

I have tried defining such index in a number of ways in RavenDB Studio, but I have had no luck. Every time, it returns all the documents of type JackpotResult instead of returning all the run.
Here is how I have initially defined the map index:

from jackpotResult in docs.JackpotResults
where jackpotResult.result != null
from categoryResult in jackpotResult.result
from divisionResult in categoryResult.Value
from run in divisionResult.Value
where run != null
select new {
    SeriesId = jackpotResult.seriesid,
    RiderId = run.riderid,
    RiderName = run.ridername,
    Date = jackpotResult.date,
    Time = run.finaltime,
    Points = run.points,
    Payout = run.payout,
    Category = run.category,
    Division = run.division,
    Position = run.position,
    HorseName = run.horsename,
    JackpotNumber = run.jackpotnumber,
    JackpotName = jackpotResult.jackpotname,
    JackpotId = jackpotResult.jackpotid
}

I have also tried to define it using SelectMany:

docs.JackpotResults.SelectMany(jackpotResult => 
    from categoryResult in jackpotResult.result
    from divisionResult in categoryResult.Value
    from run in divisionResult.Value
    where jackpotResult.seriesid != null && run != null
    select new {
        SeriesId = jackpotResult.seriesid,
        RiderId = run.riderid,
        RiderName = run.ridername,
        Date = jackpotResult.date,
        Time = run.finaltime,
        Points = run.points,
        Payout = run.payout,
        Category = run.category,
        Division = run.division,
        Position = run.position,
        HorseName = run.horsename,
        JackpotNumber = run.jackpotnumber,
        JackpotName = jackpotResult.jackpotname,
        JackpotId = jackpotResult.jackpotid
    }
)

I have tried various combinations of the 2 above to no luck. I have also defined fields for each of the lines in the select new block.

I am not totally sure what I am doing wrong, as I thought I understood how to flatten a document, but obviously, I am doing something wrong. Please let me know what the code of the map index should be.

As an FYI, I am running RavenDB 5.4.111.

Thanks a lot in advance for the help,
Bertrand.

[ayende]

A few things:

• Your issue is that you are trying to iterate over maps, right? So result is a map<string, map<string, map<string, run>> ?

For something like this, you may want to use javascript to process this directly:

function map(a) {
    let results = [];
    for (const [category, categoryData] of Object.entries(a.result)) {
        for (const [division, runs] of Object.entries(categoryData)) {
            for (const run of runs) {
                results.push({
                    SeriesId: a.seriesid,
                    RiderId: run.riderid,
                    RiderName: run.ridername,
                    Date: a.date,
                    Time: run.finaltime,
                    Points: run.points,
                    Payout: run.payout,
                    Category: run.category,
                    Division: run.division,
                    Position: run.position,
                    HorseName: run.horsename,
                    JackpotNumber: run.jackpotnumber,
                    JackpotName: a.jackpotname,
                    JackpotId: a.jackpotid
                });
            }
        }
    }
    return results;
}

I think that this what you want it to do.

Note that you are doing fanout here (which I assume is what you want).

Are you trying to search for a particular run, or find a particular jack pot matching stuff in the run?

Another option would be to output a single result, like so:

function map(a) {
    let result = {
        SeriesId: a.seriesid,
        JackpotName: a.jackpotname,
        JackpotId: a.jackpotid,
        Date: a.date,
        RiderId: [],
        RiderName: [],
        Time: [],
        Points: [],
        Payout: [],
        Category: [],
        Division: [],
        Position: [],
        HorseName: [],
        JackpotNumber: []
    };

    for (const [category, categoryData] of Object.entries(a.result)) {
        for (const [division, runs] of Object.entries(categoryData)) {
            for (const run of runs) {
                result.RiderId.push(run.riderid);
                result.RiderName.push(run.ridername);
                result.Time.push(run.finaltime);
                result.Points.push(run.points);
                result.Payout.push(run.payout);
                result.Category.push(run.category);
                result.Division.push(run.division);
                result.Position.push(run.position);
                result.HorseName.push(run.horsename);
                result.JackpotNumber.push(run.jackpotnumber);
            }
        }
    }

    return result;
}

[dbblackdiamond]

Thank you so much for the really quick response.

My backend is in Go and yes, result is defined as a map[string]map[string][]*Run. My goal would be to have such index that I can just run the following query: from index 'JackpotResultSeries/BySeriesId' where seriesid = 'series/abcdef' and it will returns all the runs from all the results associated with that series.

Looking at the javascript code of the first map function you have defined, it looks like it will do exactly what I need as it will "extract" the runs out of the result object. I tried to enter that code in RavenDB Studio and got the following error:

System.NotImplementedException: ESprima visitor doesn't support nodes of type ForOfStatement, you can override VisitUnknownNode to handle this case.

Can I just ignore that error and set VisitUnknownNode? If so, do I do that in RavenDB Studio or in the settings.json file?

Thank you so much again for the really quick turn-around.

[ayende]

You can try using this, without for of.

Note that this index by default will give you the document, not the specific run you have. You need to project for that, or store in the index

Can you provide more details about what your scenario actually is?

function map(a) {
    var results = [];
    var categories = Object.keys(a.result);
    for (var i = 0; i < categories.length; i++) {
        var category = categories[i];
        var categoryData = a.result[category];
        var divisions = Object.keys(categoryData);
        for (var j = 0; j < divisions.length; j++) {
            var division = divisions[j];
            var runs = categoryData[division];
            for (var k = 0; k < runs.length; k++) {
                var run = runs[k];
                results.push({
                    SeriesId: a.seriesid,
                    RiderId: run.riderid,
                    RiderName: run.ridername,
                    Date: a.date,
                    Time: run.finaltime,
                    Points: run.points,
                    Payout: run.payout,
                    Category: run.category,
                    Division: run.division,
                    Position: run.position,
                    HorseName: run.horsename,
                    JackpotNumber: run.jackpotnumber,
                    JackpotName: a.jackpotname,
                    JackpotId: a.jackpotid
                });
            }
        }
    }
    return results;
}

[dbblackdiamond]

Thank you again for the really quick response.

I tried to create the index using the function you provided and I got the following error:

Raven.Client.Exceptions.Documents.Indexes.IndexCreationException: JavaScript index JackpotResultSeries/BySeriesId doesn't contain any map functions or 'globalDefinition.System.Collections.Generic.Dictionary`2[System.String,System.Collections.Generic.Dictionary`2[System.String,System.Collections.Generic.List`1[Raven.Server.Documents.Indexes.Static.IndexingFunc]]]' was modified in the script
   at Raven.Server.Documents.Indexes.Static.AbstractJavaScriptIndex.ThrowIndexCreationException(String message) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\JavaScriptIndex.cs:line 419
   at Raven.Server.Documents.Indexes.Static.JavaScriptIndex.ProcessMaps(ObjectInstance definitions, JintPreventResolvingTasksReferenceResolver resolver, List`1 mapList, List`1 mapReferencedCollections, Dictionary`2& collectionFunctions) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\JavaScriptIndex.cs:line 73
   at Raven.Server.Documents.Indexes.Static.AbstractJavaScriptIndex..ctor(IndexDefinition definition, RavenConfiguration configuration, Action`1 modifyMappingFunctions, String mapCode, Int64 indexVersion) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\JavaScriptIndex.cs:line 241
   at Raven.Server.Documents.Indexes.Static.JavaScriptIndex..ctor(IndexDefinition definition, RavenConfiguration configuration, Int64 indexVersion) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\JavaScriptIndex.cs:line 36
   at Raven.Server.Documents.Indexes.Static.AbstractJavaScriptIndex.Create(IndexDefinition definition, RavenConfiguration configuration, Int64 indexVersion) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\JavaScriptIndex.cs:line 268
   at Raven.Server.Documents.Indexes.Static.IndexCompilationCache.GenerateIndex(IndexDefinition definition, RavenConfiguration configuration, IndexType type, Int64 indexVersion) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\IndexCompilationCache.cs:line 142
   at Raven.Server.Documents.Indexes.Static.IndexCompilationCache.GetIndexInstance(IndexDefinition definition, RavenConfiguration configuration, Int64 indexVersion) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\Static\IndexCompilationCache.cs:line 40
   at Raven.Server.Documents.Indexes.IndexStore.ValidateStaticIndex(IndexDefinition definition) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\IndexStore.cs:line 1045
   at Raven.Server.Documents.Indexes.IndexStore.CreateIndexInternal(IndexDefinition definition, String raftRequestId, String source) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Indexes\IndexStore.cs:line 926
   at Raven.Server.Documents.Handlers.Admin.AdminIndexHandler.PutInternal(Boolean validatedAsAdmin) in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Handlers\Admin\AdminIndexHandler.cs:line 145
   at Raven.Server.Documents.Handlers.Admin.AdminIndexHandler.PutJavaScript() in C:\Builds\RavenDB-Stable-5.4\54083\src\Raven.Server\Documents\Handlers\Admin\AdminIndexHandler.cs:line 51

This is what I am trying to do: I have this concept of a series which contains multiple events. Each event, which is called a jackpot, once finished, has a corresponding JackpotResult document. The application needs to provide overall rankings at the series level, as well as the number of runs each rider has made within the series. To do that, I need to get all the finished runs stored in the result field of the JackpotResult object. Those runs are sorted by category and then division.
I could easily do that by getting all the JackpotResult documents and processing them in Go, but i was hoping that it would be more efficient to do it in RavenDB using an index.
Another option would be to create separate documents for each finished run and then query those, which would be pretty quick, but it adds the complexity of creating those separate documents within the overall event finishing workflow.

Thanks a lot again for your help,
Bertrand.

[ayende]

Take a look at this query:

from index 'index' a
where 'RiderId' == 'users/a5s8elqetnqppahkgcqbyl'
select SeriesId,RiderId,RiderName,Date,Time,Points,Payout,Category,Division,Position,HorseName,JackpotNumber,JackpotName,JackpotId

Using this index:

map("JackpotResults", a => {
    var results = [];
    var categories = Object.keys(a.result);
    for (var i = 0; i < categories.length; i++) {
        var category = categories[i];
        var categoryData = a.result[category];
        var divisions = Object.keys(categoryData);
        for (var j = 0; j < divisions.length; j++) {
            var division = divisions[j];
            var runs = categoryData[division];
            for (var k = 0; k < runs.length; k++) {
                var run = runs[k];
                results.push({
                    SeriesId: a.seriesid,
                    RiderId: run.riderid,
                    RiderName: run.ridername,
                    Date: a.date,
                    Time: run.finaltime,
                    Points: run.points,
                    Payout: run.payout,
                    Category: run.category,
                    Division: run.division,
                    Position: run.position,
                    HorseName: run.horsename,
                    JackpotNumber: run.jackpotnumber,
                    JackpotName: a.jackpotname,
                    JackpotId: a.jackpotid
                });
            }
        }
    }
    return results;
});

With stored all fields.

You can see a full ravendb dump file (which you can import) here:

Dump of g 2025-08-09 15-16.zip

[ayende]

[dbblackdiamond]

That is amazing. It totally worked. It returns exactly what I need. Thank you so much for the help. I wouldn't not have been able to figure this out by myself. It is amazing what RavenDB can do and I am really happy with choosing it for my application.