Unwind array from documents combined with $sort and $first

Question

I had a previous post here which showed me how to retrieve the latest version of a document in a collection where the same transaction_reference may exist because of receiving each twice. (old post)

Now that I have sorted that out, I want to $unwind the documents in the set to line items and can't work out how to do it with a $first in there.

My script so far:

db.datawarehouse.aggregate(

// Pipeline
[
// Stage 1 - Narrow down set to specific document type
{
  $match: {
  "object_class" : "Goods & Services Transaction", 
  "object_type": "Transaction", 
  "object_origin_category" : "Bookkeeping"
  }
},

// Stage 2 - Narrow down by transaction date to this fiscal year
{
  $match: {
  "transaction_date" : {$gte: "2016-04-01"}, 
  "transaction_date" : {$lt: "2016-08-01"}, 
  }
},

// Stage 3 - Apply a sort so I can select latest version of document
{
  $sort: {
  "transaction_reference":1, 
  "object_creation_date": -1
  }
},

// Stage 4 - get transaction header information of only latest version of document
{
  $group: {
  _id: "$transaction_reference", 
  "party_uuid" : {$first: "$party_uuid"},
  "transaction_date" : {$first: "$transaction_date"},
  "transaction_net_value" : {$first: "$transaction_net_value"},
  "object_category" : {$first: "$object_category"},
  "transaction_status" : {$first: "$transaction_status"},
  "object_origin_category" : {$first: "$object_origin_category"},
  "object_origin" : {$first: "$object_origin"},
  "customer" : {$first: "$party.customer.customer_name"}
  }
}

]

);

A stage 5 - unwind transaction required

I now want to apply an $unwind to the set on an array called goods_and_services but cant's figure out how. I'm a bit rusty on this, haven't done Mongo stuff for ages.

An example document below. There are two other examples of documents on the other post as linked above.

{
"_id": {
    "$oid": "579aa337f36d2808839a05e8"
},
"object_class": "Goods & Services Transaction",
"object_category": "Revenue",
"object_type": "Transaction",
"object_origin": "Sage One",
"object_origin_category": "Bookkeeping",
"object_creation_date": "2016-07-05T00:00:00.201Z",
"party_uuid": "dfa1e80a-5521-11e6-beb8-9e71128cae77",
"connection_uuid": "b945bd7c-7988-4d2a-92f5-8b50ab218e00",
"transaction_reference": "SI-1",
"transaction_status": "UNPAID",
"transaction_date": "2016-06-16T00:00:00.201Z",
"transaction_due_date": "2016-07-15T00:00:00.201Z",
"transaction_currency": "GBP",
"goods_and_services": [
    {
        "item_identifier": "PROD01",
        "item_name": "Product One",
        "item_quantity": 1,
        "item_gross_unit_sale_value": 1800,
        "item_revenue_category": "Sales Revenue",
        "item_net_unit_cost_value": null,
        "item_net_unit_sale_value": 1500,
        "item_unit_tax_value": 300,
        "item_net_total_sale_value": 1500,
        "item_gross_total_sale_value": 1800,
        "item_tax_value": 300
    }
],
"transaction_gross_value": 1800,
"transaction_gross_curr_value": 1800,
"transaction_net_value": 1500,
"transaction_cost_value": null,
"transaction_payments_value": null,
"transaction_payment_extras_value": null,
"transaction_tax_value": 300,
"party": {
    "customer": {
        "customer_identifier": "11",
        "customer_name": "KP"
    }
}
}

Thanks in advance

actually, I've just moved the `$unwind` to stage 3 before the sort and I think it's working, still testing. Thanks — Matt Lightbourn, Jul 31 '16 at 00:28
Moving the `$unwind` before doesn't help. It's now only selecting `$first` of the unwound set. I have to have `$unwind` afterwards. — Matt Lightbourn, Jul 31 '16 at 00:33
Why do you want to `$unwind` `goods_and_services` ? which brings the question : What do you want to do with `goods_and_services` ? — Bertrand Martel, Jul 31 '16 at 00:43
What I'm trying to do is to end up with a document per line item from the latest version of transaction documents. I need to then `$match` by `item_revenue_category` and sum by `transaction_date`. — Matt Lightbourn, Jul 31 '16 at 00:46

score 0 · Answer 1 · answered Jul 31 '16 at 00:56

Thank you @Bertrand Mantel for your interest and help. I have just sussed it.

On stage 4, I needed to add "goods_and_services" : {$first: "$goods_and_services"}

Then I added stage 5 as {$unwind: "$goods_and_services"} and I've got exactly the right number of rows now. I have to $project the right values for the rows but I'm pretty close. Thanks again

Unwind array from documents combined with $sort and $first

1 Answers1