# Multi Field Look-ups! low-code Transform Stage

In the [previous Example](/integration-hub/use-cases/follow-along-examples/look-ups-no-code-transform-stage.md) 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.

<figure><img src="/files/Z8b6hbAvcZQHcnpBQxhH" alt=""><figcaption></figcaption></figure>

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

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.&#x20;

now you can take a look [here](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/), 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.&#x20;

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:

{% code overflow="wrap" %}

```
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.
```

{% endcode %}

It has given the the following code

```json
[
  {
    "$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

<figure><img src="/files/waFYgRZQz6NKXn6c4Hfu" alt=""><figcaption></figcaption></figure>

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

{% code overflow="wrap" %}

```
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.
```

{% endcode %}

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

{% code overflow="wrap" %}

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

{% endcode %}

Tried the next code and SUCCESS!&#x20;

```json
[
  {
    "$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:

<figure><img src="/files/xsO3ZrDx0Vt0VvOmaz3a" alt=""><figcaption></figcaption></figure>

We now have an accurate multi field lookup ready for use!&#x20;

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://university.utopiasync.com/integration-hub/use-cases/follow-along-examples/look-ups-no-code-transform-stage/multi-field-look-ups-low-code-transform-stage.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.