Group By for a Existing Facet Query

Working with Data
1

I have this data:

{
  "_id": {
    "$oid": "6397d344742db0acbdc8ce42"
  },
  "item_id": 1,
  "box_id": 1,
  "name": "NZ",
  "condition": {
    "name": "A",
    "id": 5
  },
  "apple_date": null,
  "apple_size_kilograms": null,
  "orange_date": "2018-12-17",
  "orange_size_kilograms": 325,
},{
  "_id": {
    "$oid": "6397d344742db0acbdc8ce43"
  },
  "item_id": 1,
  "box_id": 1,
  "name": "NZ",
  "condition": {
    "name": "A",
    "id": 5
  },
  "apple_date": "2010-11-06",
  "apple_size_kilograms": 352,
  "orange_date": "2008-11-29",
  "orange_size_kilograms": 234,
}{
  "_id": {
    "$oid": "6397d344742db0acbdc8ce47"
  },
  "item_id": 3,
  "box_id": 1,
  "name": "US",
  "condition": {
    "name": "F",
    "id": 7
  },
  "apple_date": "2017-09-17",
  "apple_size_kilograms": 342,
  "orange_date": "2017-06-24",
  "orange_size_kilograms": 344,
}

I have this aggregation query, that gets me each item, with the lowest values in each category.

[
  {
    $match: {
      box_id: 1,
      item_id: 1,
    },
  },
  {
    $facet: {
      apple: [
        {
          $match: {
            apple_size_kilograms: {
              $ne: null,
            },
          },
        },
        {
          $sort: {
            apple_size_kilograms: 1,
          },
        },
        {
          $limit: 1,
        },
      ],
      orange: [
        {
          $match: {
            orange_size_kilograms: {
              $ne: null,
            },
          },
        },
        {
          $sort: {
            orange_size_kilograms: 1,
          },
        },
        {
          $limit: 1,
        },
      ],
    },
  },
  {
    $unwind: "$apple",
  },
  {
    $unwind: "$orange",
  },
]

My issues is thats just a example of the output I am after, I dont want to filter by item_id, i actually want to group by item_id. but I cant work out how. I have tried every method I can think so. Can anyone point my in the right direction please.

I am really after my output to look like:

[
{
	1:
	{ apple: {the_whole document_from_this_item-id_apple_with_the_lowest_kilograms},
	{ orange: {the_whole document_from_this_item-id_orange_with_the_lowest_kilograms},
},{
	3:
	{ apple: {the_whole document_from_this_item-id_apple_with_the_lowest_kilograms},
	{ orange: {the_whole document_from_this_item-id_orange_with_the_lowest_kilograms},
}

Please let me know if I have posted this wrong.

thanks

2

One idea is to forgo $facet as the main stage and start with a $group with _id:$item_id and then for each group perform a $lookup with a pipeline that $facet, $sort and $limit. Something that might look like:

{ "$group" :  {
  "_id" : "$item_id"
} } ,
{ "$lookup" :  {
  "from" : collection ,
  "as" : "result" ,
  "localField : "_id" ,
  "foreignField" : "_id"
  "pipeline" : [
    localFeld
  ]
} }
5

Looks like I went to sleep before terminating my last post in this thread. B-(

Please forgive me. I will give it another try soon.

6

I am back.

group = { "$group" :  {
  "_id" : "$item_id"
} }

orange = [
  { "$match" : { "orange_size_kilograms" : { "$ne" : null } } } ,
  { "$sort" : { "orange_size_kilograms" : 1 } } ,
  { "$limit" : 1 }
]

apple = [
  { "$match" : { "apple_size_kilograms": { "$ne" : null } } } ,
  { "$sort" : { "apple_size_kilograms" : 1 } } ,
  { "$limit" : 1 }
]

facet = { "$facet" : { orange , apple }}

{ "$lookup" : {
  "from" : "the-same_collection" ,
  "localField" : "_id" ,
  "foreignField" : "item_id" ,
  "as" : "result" ,
  "pipeline" :  [ facet ]
} }

pipeline = [ group , lookup ]

This seems to extract the correct information. The format is not exactly want you want but some $unwind and $set and $unset stages should bring you pretty close.

1 Like