Grouping

Grouping is a special case of reduce,where we reduce in an object with keys the ids and values,arrays with the members of the group(that had same id).

For examplpe one array like the above,can be groupped by age

[
{ name: 'Alice', age: 21 },
{ name: 'Max', age: 20 },
{ name: 'Jane', age: 20 }
]

Resulting in 1 object

{
20 : [{ name: 'Max', age: 20 } { name: 'Jane', age: 20 }]
21 : [{ name: 'Alice', age: 21 }]
}

MQL doesn't provide a straight forward way to group arrays fast.

  • missing document operators that we could use like contains etc to make a solution like the bellow javascript code
  • missing a group operator for arrays,we have only group stage operator for collections

Alternatives

  • use unwind and stage group operators slow and complicated,those operators changes the collection and the document structure for limitations and problems with this approach see also
  • Use javascript for example
function groupBy(objectArray, property) {
return objectArray.reduce(function (acc, obj) {
let key = obj[property]
if (!acc[key]) {
acc[key] = []
}
acc[key].push(obj)
return acc
}, {})
}
  • cMQL group-array operator

cMQL provides a group-array operator that uses lookup with pipeline,facet,unwind,group and needs 1 dummy collection with 1 document.

Its fast and with get-in , assoc-in operators we can get the array from any location,group it, and put it back very fast and simple.

cMQL group array

Example1

Grouping a not nested array

(insert :testdb.testcoll [{:myarray [{:name 1 :age 20} {:name 2 :age 20} {:name 3 :age 25}]}
{:myarray [{:name 4 :age 30} {:name 5 :age 40} {:name 6 :age 40}]}])
(c-print-all
(q :testdb.testcoll
(group-array :myarray
:age ;;the path that i want to group-by,here its :myarray.age
{:people (conj-each :a)}
:mygroups)))

Results in

{:myarray [{:name 1, :age 20} {:name 2, :age 20} {:name 3, :age 25}],
:mygroups
[{:_id 20, :people [{:name 1, :age 20} {:name 2, :age 20}]}
{:_id 25, :people [{:name 3, :age 25}]}]}
{:myarray [{:name 4, :age 30} {:name 5, :age 40} {:name 6, :age 40}],
:mygroups
[{:_id 30, :people [{:name 4, :age 30}]}
{:_id 40, :people [{:name 5, :age 40} {:name 6, :age 40}]}]}

:myarray of every document is grouped in :mygroups an array of the groups

If we $reduce and merge objects we can get 1 document(for the first document of the collection) like { "20" [{:name 1, :age 20} {:name 2, :age 20}] "25" [{:name 3, :age 25}]}] }

But we use unknown fields(keys) and its not prefered for MQL.

Example2

Group a nested array

(drop-collection :testdb.testcoll)
(insert :testdb.testcoll [{:mymixedarray [1 {:a "b" :c {:d [{:age 1} {:age 2} {:age 3} {:age 2}]}}]}
{:mymixedarray [1 {:a "b" :c {:d [{:a 1} {:a 2} {:a 3} {:a 2}]}}]}
{:mymixedarray [1 {:a "b" :c {:d [1 2 3]}}]}
{:mymixedarray [1 2]}])
(c-print-all
(q :testdb.testcoll
(group-array (get-in :mymixedarray [1 "c" "d"])
:age ;;i only add the extra path
{:people (conj-each :a)}
:people-groups)
{:mymixedarray (if- (not-empty? :people-groups)
(assoc-in :mymixedarray [1 "c" "d"] :people-groups)
:mymixedarray)}
(unset :people-groups)))

Results in

{:mymixedarray
[1
{:a "b",
:c
{:d
[{:_id 2, :people [{:age 2} {:age 2}]}
{:_id 1, :people [{:age 1}]}
{:_id 3, :people [{:age 3}]}]}}]}
{:mymixedarray
[1
{:a "b",
:c {:d [{:_id nil, :people [{:a 1} {:a 2} {:a 3} {:a 2}]}]}}]}
{:mymixedarray [1 {:a "b", :c {:d [{:_id nil, :people [1 2 3]}]}}]}
{:mymixedarray [1 2]}

We took the :d array with get-in,we grouped it,and put it back where it was with assoc-in The second document doesn't have the same structure but this doesn't cause any problems group-array is done only when [1 "c" "d"] is found and its an array.

The condition (if- (not-empty? :people-groups) ...) is not needed if we always know that :d exists and its an array.

It works even if :d doesnt exists.If its an array with documents with out the property, we group and all goes under nil , so 1 group.User can decide to not replace it in this case.