CSOM list query with JOIN, return tree structure

Question

I have two lists, Parent and Child. The Child list has a lookup field called "Parent", which "looks into" the Parent list, using ID column.

Using CSOM I'm joining the lists with this query

<View>
    <Joins>
        <Join Type='LEFT' ListAlias='Parent'> 
            <Eq>
                <FieldRef Name='Parent' RefType='ID' />
                <FieldRef List='Parent' Name='ID' />
            </Eq>
        </Join>
    </Joins>
</View>

What returns is something along the lines of

[
    {Title: "Child 1.1", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.2", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.3", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.4", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 2.1", Parent: "Parent 2", Parent_x003a_Desc: "foo"},
    {Title: "Child 2.2", Parent: "Parent 2", Parent_x003a_Desc: "foo"}
]

Where the Parent values are duplicated in each row and there's no structure.

Is it at all possible to retrieve a structured response, e.g.

[
    {
        Title: "Parent 1", 
        Parent_x003a_Desc: "foo",
        Children: [
            {Title: "Child 1.1"},
            {Title: "Child 1.2"},
            {Title: "Child 1.3"},
            {Title: "Child 1.4"}
        ]
    },

    {
        Title: "Parent 2", 
        Parent_x003a_Desc: "foo",
        Children: [
            {Title: "Child 2.1"},
            {Title: "Child 2.2"}
        ]
    }
]

Answer 1

It is possible to transform specified result, the following function could be used for that purpose (jQuery is used):

//Filter results by Parent  
function filteredByParent(entries) {
    var filteredEntries = [];
    var childEntries = [];
    $.each(entries, function (i, entry) {
        var groupKey = entry.Parent;
        if (typeof (childEntries[groupKey]) === "undefined") {
            childEntries[groupKey] = [];
            filteredEntries.push({ Title: entries[i].Parent, Parent_x003a_Desc: entries[i].Parent_x003a_Desc, Children: childEntries[groupKey] });
        }
        childEntries[groupKey].push(entries[i].Title);
    });
    return filteredEntries;
}

Example

For result

[
    {Title: "Child 1.1", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.2", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.3", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 1.4", Parent: "Parent 1", Parent_x003a_Desc: "foo"},
    {Title: "Child 2.1", Parent: "Parent 2", Parent_x003a_Desc: "foo"},
    {Title: "Child 2.2", Parent: "Parent 2", Parent_x003a_Desc: "foo"}
]

stored in variable entries use the following call to transform it to the expected structure (saved in variable filteredEntries)

var filteredEntries = filteredByParent(entries);

Answer 2

If you use CSOM, you can only use the client side transformation to create tree structured objects. You have a good working example from @vgrem. Just for completeness I want to provide a javascript function which doesn't have the jQuery dependency:

function transform(items) {
    var transformed = [],
        childEntries = [],
        i = 0,
        len = items.length;
        process = function(item) {
            var groupKey = item.Parent;
            if ( !childEntries[groupKey] ) {
                childEntries[groupKey] = [];
                transformed.push({ 
                    Title: item.Parent, 
                    Parent_x003a_Desc: item.Parent_x003a_Desc, 
                    Children: childEntries[groupKey] 
                });
            }
            childEntries[groupKey].push(item.Title);
        }
    for (i; i < len; i++) {
        process( items[i] );
    }
    return transformed;
}

BTW, when you process your data in javascript, you can take an advantage of the new array functions defined in ecmascript 5 like: map, reduce, sort, filter, every...

The transform function I've provided doesn't use the ecmascript 5 functions, because SharePoint is often run in older IE browsers. If you are interested in the new functions you can try underscorejs or sugarjs which can give you a LINQ experience in javascript. Here is the same transform function which uses underscorejs (_) to group your flatten objects and project them into the new tree structured objects:

function transform(items) {
    //group your original items
    var grouped = _.groupBy(items, 'Parent');

    //project into new objects, map would be "Select" in LINQ
    var transformed = _.map(grouped, function(g) {
        return { 
            Title: g[0].Parent, 
            Parent_x003a_Desc: g[0].Parent_x003a_Desc,
            Children: g
        }
    });
    return transformed;
}

Both my transform functions, and @vgreem's filteredByParent return the same result. I thought it would a good comparison of different ways of data processing in javascript.