Group by specific value inside array inside another array in MongoDB

Question

I am trying to write a query to group my records by the value inside an array inside another array inside a collection in MongoDB. Now if that doesn't have your head hurting I think a sample schema might be easier to understand:

{
    "_id": ObjectId("...")
    "attributes": [
        [ "attributeA", "valueA" ],
        [ "attributeB", "valueB" ],
        [ "attributeC", "valueC" ],
        ...
    ]
}

Now I want to be able to group my records by the attributeB field based on valueB.

So far I can aggregate if I know the actual value of valueB:

collection.aggregate([
    { '$match': { 'attributes': [ "attributeB", "valueB" ] } },
    { '$group': {
        '_id': { 'attributes': [ "attributeB", "valueB" ] } } 
    }  
])

Basically seeing if the attributes array contains the pair: [ "attributeB", "valueB" ]. But now I want to be able to have the query determine what valueB is as it performs the aggregation.

To paraphrase: I can't seem to figure out how to group by the value if I don't know the value of valueB. I just want all records to group by their valueB's, when attributeB is found at the first position inside an array inside the attributes array.

Any help is appreciated. Thanks!

Your document structure seems to be incorrect though you might have a definite reason for putting it that way. Why not make attributes array :[{"attributeA":"valueA"},{"attributeB":"valueB"}] or "attributes":{"attributeA":"valueA","attributeB":"valueB"} ? It would make querying a lot easier. — BatScream
– BatScream, Commented Jan 27, 2015 at 18:55
I didn't want to restrict the data type of an element in the attributes array. It could be anything, not necessarily a key:value pair. — Andrew
– Andrew, Commented Jan 27, 2015 at 19:36

Community · Accepted Answer · 2017-05-23 12:12:50Z

1

After grouping your data you should use the $unwind operator. It pairs up your other fields with every item in the array.

collection.aggregate([
  { '$match': { 'attributes': [ "attributeB", "valueB" ] } },
  { '$group': {
      '_id': { 'attributes': [ "attributeB", "valueB" ] } } 
  },
  { '$unwind':  'attributes'},
    ... // here you can match again and continue aggregation
])

Most probably this is not the fastest solution. I will think of a better one.

Also note that the order of elements in the array is not preserved.

UPDATE

This is a similar question. So what I would do is create documents in the attribute array like

'attributes':[
   {'attribute': 'attributeB', 'value': 'valueB'},
   {'attribute': 'attributeC', 'value': 'valueA'},
]

So you can access your valueB after the $match or $unwind through $value.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Jan 27, 2015 at 18:03

Pio

4,06411 gold badges48 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

Thank you for your reply, it solves another part of the problem I have but not the main one. My main problem is that I don't want my query to match with "valueB" explicitly. I want every instance of "valueB" to be grouped together implicitly by the query.

Collectives™ on Stack Overflow

Group by specific value inside array inside another array in MongoDB

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related