Filtering based on multiple arrays - Advanced

How To
,
1

I need to filter out all requisitions and orderlines where Requisitions.OrderLines.RequisitionItemNumber = PurchaseOrderNumber.OrderLines.RequisitionItemNumber

is it possible using a minimum of iterators?

This json

[
    {
        "data": {
            "Requisitions": [
                {
                    "RequisitionNumber": "REQ-1",
                    "Description": "Computer",
                    "OrderLines": [
                        {
                            "RequisitionItemNumber": "100",
                            "Quantity": 1,
                            "Price": 50,
                            "Discount": 0
                        },
                        {
                            "RequisitionItemNumber": "200",
                            "Quantity": 1,
                            "Price": 60,
                            "Discount": 0
                        }
                    ]
                }
            ],
            "PurchaseOrders": [
                {
                    "PurchaseOrderNumber": "PO-1",
                    "FreightPrice": 50,
                    "OrderLines": [
                        {
                            "RequisitionItemNumber": "100"
                        },
                        {
                            "RequisitionItemNumber": "300"
                        }
                    ]
                }
            ]
        }
    }
]

Should filter and return this:
(could return more than one requisition and/or orderlines)

{
	"Requisitions": [
	{
		"RequisitionNumber": "REQ-1",
		"Description": "Computer",
		"OrderLines": [
			{
				"RequisitionItemNumber": "100",
				"Quantity": 1,
				"Price": 50,
				"Discount": 0
			}
	}
}

because
Requisitions.OrderLines.RequisitionItemNumber = 100 and
PurchaseOrderNumber.OrderLines.RequisitionItemNumber = 100

2

Yes, it’s possible.

  1. First, use a JSON Parse module to parse your input JSON.

  2. Then, use an Iterator module to iterate through the Requisitions array.

  3. Inside the Iterator, use a Filter module with the following condition:

{{some(map(map(item.data.PurchaseOrders; 'po'; po.OrderLines); 'ol'; ol.RequisitionItemNumber); '=='; item.OrderLines.RequisitionItemNumber)}}

This filter checks if any RequisitionItemNumber in the current Requisition’s OrderLines matches any RequisitionItemNumber in any PurchaseOrder’s OrderLines.

  1. After the filter, use an Array aggregator to collect the filtered results.

  2. Finally, use a JSON Transform module to format the output as desired.

Here’s a sample JSON Transform formula:

{
  "Requisitions": map(1.data.Requisitions; 'req';
    {
      "RequisitionNumber": req.RequisitionNumber,
      "Description": req.Description,
      "OrderLines": filter(req.OrderLines; 'ol';
        some(map(1.data.PurchaseOrders; 'po';
          map(po.OrderLines; 'pol'; pol.RequisitionItemNumber)
        ); '=='; ol.RequisitionItemNumber)
      )
    }
  )
}

This approach uses only one iterator (for the Requisitions array) and leverages Make’s built-in functions to perform the filtering and transformation. The some() function checks if any element in an array satisfies a condition, while map() is used to transform arrays. The filter() function is used to keep only the matching OrderLines.

3

Thank. Never heard of some() and filter() functions. Is this tested code?

4

The Ai does not make mistakes :wink: