CODEWITHSUNDEEP
arraysmongodbaggregation-framework
Matt Lightbourn
Matt Lightbourn

Reputation: 597

Aggregate from one document array to another

I am trying to do a transformation from one data structure to another and am using NoSQL Booster as I feel more comfortable using SQL queries than I do using Mongo script, a little lazy but its worked for me thus far.

I have a document which has a an array of line items which represents a sales receipt. The line items are the items sold.

I have used the following SQL query to generate Mongo script to select and then rename values from one document to create another. Where I'm stuck is in selecting from an array some fields and renaming them into a new document within a new array.

mb.runSQLQuery(`

   SELECT   object_origin, 
            party_uuid AS company,
            connection_uuid AS connection,
            object_created AS object_creation_date,
            "object_raw_origin_data.register_sales.sale_date" AS transaction_date,
            "object_raw_origin_data.register_sales.total_price"
            + "object_raw_origin_data.register_sales.total_tax" AS transaction_gross_value,
            "object_raw_origin_data.register_sales.total_price" AS transaction_net_value,
            'goods-service-transaction' AS object_class,
            'point-of-sale' AS object_origin_category,
            'offline' AS object_origin_type,
            object_origin AS object_origin,
            "object_raw_origin_data.register_sales.invoice_number" AS transaction_reference,
            "object_raw_origin_data.register_sales.status" AS transaction_status,
            'GBP' AS transaction_currency,
            "object_raw_origin_data.register_sales.sale_products.name" AS 'line_items.item_name'

   FROM Vend_raw_transactions

`)

The query above works all except the last line which creates an object in the document called line_items but it isn't an array. The source is also an array.

Below is the Mongo script it creates for me:

  db.Vend_raw_transactions.aggregate(
[{
  "$project": {
    "object_origin": "$object_origin",
    "company": "$party_uuid",
    "connection": "$connection_uuid",
    "object_creation_date": "$object_created",
    "transaction_date": "$object_raw_origin_data.register_sales.sale_date",
    "transaction_gross_value": {
      "$add": [
        "$object_raw_origin_data.register_sales.total_price",
        "$object_raw_origin_data.register_sales.total_tax"
      ]
    },
    "transaction_net_value": 
"$object_raw_origin_data.register_sales.total_price",
    "object_class": "goods-service-transaction",
    "object_origin_category": "point-of-sale",
    "object_origin_type": "offline",
    "transaction_reference": 
"$object_raw_origin_data.register_sales.invoice_number",
    "transaction_status": "$object_raw_origin_data.register_sales.status",
    "transaction_currency": "GBP",
    "line_items.item_name": 
"$object_raw_origin_data.register_sales.sale_products.name"
 }
}])

Does anyone know why the last line isn't working for me? 

"line_items.item_name": "$object_raw_origin_data.register_sales.sale_products.name"

I'm pretty sure I'm missing an [*] somewhere. Thanks, Matt

EDITED TO ADD example of Vend Transaction

{ 
"object_category" : "application", 
"object_type" : "register-sales-24-months", 
"object_origin" : "vend", 
"tenant_uuid" : "00000000-0000-0009-9999-999999999999", 
"party_uuid" : "8d519765-05d2-469f-ad35-d7a22fa9df2f", 
"subscription_uuid" : "0", 
"connection_uuid" : "6ed9bd79-d9c5-4296-a821-7e15b1c69e6c", 
"status" : "", 
"object_created" : ISODate("2018-03-15T21:40:57.158+0000"), 
"object_raw_origin_data" : {
    "pagination" : {
        "results" : NumberInt(75964), 
        "page" : NumberInt(1), 
        "page_size" : NumberInt(200), 
        "pages" : NumberInt(380)
    }, 
    "register_sales" : {
        "id" : "776a66f2-993c-b372-11e8-26f9bbe253f4", 
        "source" : "USER", 
        "source_id" : "", 
        "register_id" : "02dcd191-ae55-11e6-edd8-ec8dce1d9e1c", 
        "market_id" : "3", 
        "customer_id" : "02d59481-b67d-11e5-f667-b08185e8f6d5", 
        "customer_name" : "", 
        "customer" : {
            "id" : "02d59481-b67d-11e5-f667-b08185e8f6d5", 
            "name" : "", 
            "customer_code" : "WALKIN", 
            "customer_group_id" : "02d59481-b67d-11e5-f667-b08185e893f8", 
            "customer_group_name" : "All Customers", 
            "updated_at" : "2016-01-01 12:16:44", 
            "deleted_at" : "", 
            "balance" : "0", 
            "year_to_date" : "0", 
            "date_of_birth" : "", 
            "sex" : "", 
            "custom_field_1" : "", 
            "custom_field_2" : "", 
            "custom_field_3" : "", 
            "custom_field_4" : "", 
            "note" : "", 
            "contact" : {
            }
        }, 
        "user_id" : "02d59481-b655-11e5-f667-dca974edc4ea", 
        "user_name" : "Alvaro Velosa", 
        "sale_date" : "2018-03-13 20:04:57", 
        "created_at" : "2018-03-13 20:05:08", 
        "updated_at" : "2018-03-13 20:05:08", 
        "total_price" : 3.5, 
        "total_cost" : 2.74, 
        "total_tax" : NumberInt(0), 
        "tax_name" : "No Tax", 
        "note" : "", 
        "status" : "CLOSED", 
        "short_code" : "newa6f", 
        "invoice_number" : "Masonic2107Temple", 
        "accounts_transaction_id" : "", 
        "return_for" : "", 
        "register_sale_products" : [
            {
                "id" : "776a66f2-993c-b372-11e8-26f9cbf10f10", 
                "product_id" : "02dcd191-ae55-11e7-f130-9d4f4bcd91b1", 
                "register_id" : "02dcd191-ae55-11e6-edd8-ec8dce1d9e1c", 
                "sequence" : "0", 
                "handle" : "LAGERDRAUGHT300", 
                "sku" : "10287", 
                "name" : "LAGER DRAUGHT £3.00", 
                "quantity" : NumberInt(1), 
                "price" : 3.5, 
                "cost" : 2.74, 
                "price_set" : NumberInt(0), 
                "discount" : NumberInt(0), 
                "loyalty_value" : NumberInt(0), 
                "tax" : NumberInt(0), 
                "tax_id" : "02d59481-b67d-11e5-f667-b08185ec2871", 
                "tax_name" : "No Tax", 
                "tax_rate" : NumberInt(0), 
                "tax_total" : NumberInt(0), 
                "price_total" : 3.5, 
                "display_retail_price_tax_inclusive" : "0", 
                "status" : "CONFIRMED", 
                "attributes" : [
                    {
                        "name" : "line_note", 
                        "value" : ""
                    }
                ]
            }
        ], 
        "totals" : {
            "total_tax" : NumberInt(0), 
            "total_price" : 3.5, 
            "total_payment" : 3.5, 
            "total_to_pay" : NumberInt(0)
        }, 
        "register_sale_payments" : [
            {
                "id" : "776a66f2-993c-b372-11e8-26f9cd75e9aa", 
                "payment_type_id" : "1", 
                "register_id" : "02dcd191-ae55-11e6-edd8-ec8dce1d9e1c", 
                "retailer_payment_type_id" : "02d59481-b655-11e5-f667-b0a23bc0e7bc", 
                "name" : "Cash", 
                "label" : "Account Customer", 
                "payment_date" : "2018-03-13 20:04:57", 
                "amount" : NumberInt(10)
            }, 
            {
                "id" : "776a66f2-993c-b372-11e8-26f9cd7a3096", 
                "payment_type_id" : "1", 
                "register_id" : "02dcd191-ae55-11e6-edd8-ec8dce1d9e1c", 
                "retailer_payment_type_id" : "02d59481-b655-11e5-f667-b0a23bc0e7bc", 
                "name" : "Cash", 
                "label" : "Account Customer", 
                "payment_date" : "2018-03-13 20:04:57", 
                "amount" : -6.5
            }
        ]
    }
}
}

Upvotes: 1

Views: 312

Answers (1)

chridam
chridam

Reputation: 103335

Use the $map operator to shape the field to an array by mapping the array elements to the item_name key:

"line_items": {
    "$map": {
        "input": "$object_raw_origin_data.register_sales.register_sale_products",
        "as": "product",
        "in": { "item_name": "$$product.name" }
    }
}

Output

"line_items" : [ 
    {
        "item_name" : "LAGER DRAUGHT £3.00"
    }
]

Upvotes: 1

Related Questions