N1QL Trying to join multiple sub records

SQL++
1

Hi.

I have data as:

{
    _id: "",
    prop: "",
    prop2: "",
    subids: [
        "id1",
        "id2",
        "id3"
    ]
}

What I want to do is pull this record, but replace the subids array with properties from the other objects that the ids in the “subids” array reference.

For instance, the resultant data should be:

{
    _id: "",
    prop: "",
    prop2: "",
    names: [
        {id: "id1", name: "nameAssociatedWithId1"},
        {id: "id2", name: "nameAssociatedWithId2"},
        {id: "id3", name: "nameAssociatedWithId3"}
    ]
}

The current query I’m working with is just joining on the “subids” array, but returns a new record for each matching subid. This is problematic because the document may be rather large, and I don’t want to pull it out of the database multiple times, and have it sit in memory multiple times. The issue gets even more dramatic if the number of subids are very large.

For instance, this is what I have working in terminal for testing.

select c._id, u._id AS refid, u._name from defeault c JOIN default u ON KEYS c.subids;

Thanks, any help is appreciated.

2

Bump… @geraldss or anyone else?

3

Sorry we missed this. Taking a look now.

4

Replace JOIN with NEST: