Multi Field Look-ups! low-code Transform Stage

In the previous Example we looked at using the no-code tools to build a lookup stage. This has its limitations so here we are going to look at elaborating this into a low-code stage where we can do a lookup to match 2 fields using a mongodb aggregation. Please see the previous example for all the input files required for this tutorial.

To do this, the only extra information we will need will be to get the collection ID of the PriceMapping collection, so lets go back into the Integration Hub and edit that collection.

Here is the collection ID! lets copy that and save it to a notepad.

Next step is to get the document format from this collection, I am just going to download it from the transaction page. On the transaction page expand one of the PriceMapping entries (where it says 3) this will expand and show you all the documents that we un-batched. click the menu button, and view one of these entries. Now hit the download button to save it to your hard-drive.

now you can take a look here, and navigate the syntax to build the aggregation yourself, but I am going to be lazy and just ask AI to do it for me.

To do this I am going to give it the Cafe Orders In collection json format, the fields I want to match and the format of the lookup collection along with its ID. Important to ask it to give you the output in a valid JSON format.

My Prompt, I then attached the two files referenced:

I need to a mongodb aggregation written in valid json format. This aggregation needs to take document: Orders_In.json and do a lookup from collection 'e7cf6211-6154-44dd-a3eb-896cfc7380ce' which contains documents with format PriceMapping.json. 

The lookup must match AccountNumber with CustomerCode and then OrderItems.ItemNumber with ProductCode.

The output should add the matched document to a field within OrderItems called PriceLookup, there should be a response for each OrderItems.

It has given the the following code

[
  {
    "$unwind": "$OrderItems"
  },
  {
    "$lookup": {
      "from": "e7cf6211-6154-44dd-a3eb-896cfc7380ce",
      "let": {
        "accountNumber": "$AccountNumber",
        "itemNumber": "$OrderItems.ItemNumber"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                { "$eq": ["$CustomerCode", "$$accountNumber"] },
                { "$eq": ["$ProductCode", "$$itemNumber"] }
              ]
            }
          }
        }
      ],
      "as": "OrderItems.PriceLookup"
    }
  },
  {
    "$unwind": "$OrderItems.PriceLookup"
  },
  {
    "$group": {
      "_id": "$_id",
      "CartID": { "$first": "$CartID" },
      "TransactionReference": { "$first": "$TransactionReference" },
      "OrderReference": { "$first": "$OrderReference" },
      "OrderNumber": { "$first": "$OrderNumber" },
      "InvoiceName": { "$first": "$InvoiceName" },
      "InvoiceCity": { "$first": "$InvoiceCity" },
      "InvoiceCountry": { "$first": "$InvoiceCountry" },
      "InvoiceAddress1": { "$first": "$InvoiceAddress1" },
      "OrderDate": { "$first": "$OrderDate" },
      "OrderTotal": { "$first": "$OrderTotal" },
      "AccountNumber": { "$first": "$AccountNumber" },
      "OrderStatus": { "$first": "$OrderStatus" },
      "CurrencyCode": { "$first": "$CurrencyCode" },
      "TotalLines": { "$first": "$TotalLines" },
      "OrderItems": { "$push": "$OrderItems" }
    }
  }
]

I am going to paste this into transform stage, changing its type to js/json

Great we have success, the output now only has the correct data matching

This new stage looks great, it just has some unwanted system fields in there which we do not need for this scenario, such as _id and header fields. So we just need to remove those from the file, I am just going to ask AI to remove them

The output is correct except inside the OrderItems.PriceLookup result, it contains _id and _header fields from the lookup document which are not needed. please remove these.

I got another response, which I tested and got an elmah error. NB: remove comments from the document before pasting it into UtopiaSync JS/JSON stage. Jumped into Elmah to get the error and found it. Prompted AI again

OK I am getting an error: Invalid $project :: caused by :: Cannot do inclusion on field CustomerCode in exclusion projection

Tried the next code and SUCCESS!

[
  {
    "$unwind": "$OrderItems"
  },
  {
    "$lookup": {
      "from": "e7cf6211-6154-44dd-a3eb-896cfc7380ce",
      "let": {
        "accountNumber": "$AccountNumber",
        "itemNumber": "$OrderItems.ItemNumber"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                { "$eq": ["$CustomerCode", "$$accountNumber"] },
                { "$eq": ["$ProductCode", "$$itemNumber"] }
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            "_header": 0
          }
        }
      ],
      "as": "OrderItems.PriceLookup"
    }
  },
  {
    "$unwind": "$OrderItems.PriceLookup"
  },
  {
    "$group": {
      "_id": "$_id",
      "CartID": { "$first": "$CartID" },
      "TransactionReference": { "$first": "$TransactionReference" },
      "OrderReference": { "$first": "$OrderReference" },
      "OrderNumber": { "$first": "$OrderNumber" },
      "InvoiceName": { "$first": "$InvoiceName" },
      "InvoiceCity": { "$first": "$InvoiceCity" },
      "InvoiceCountry": { "$first": "$InvoiceCountry" },
      "InvoiceAddress1": { "$first": "$InvoiceAddress1" },
      "OrderDate": { "$first": "$OrderDate" },
      "OrderTotal": { "$first": "$OrderTotal" },
      "AccountNumber": { "$first": "$AccountNumber" },
      "OrderStatus": { "$first": "$OrderStatus" },
      "CurrencyCode": { "$first": "$CurrencyCode" },
      "TotalLines": { "$first": "$TotalLines" },
      "OrderItems": { "$push": "$OrderItems" }
    }
  }
]

Check out the response on the Designer:

We now have an accurate multi field lookup ready for use!

I hope this tutorial was helpful, help us out and let us know what you would like to see next!