Joining lists by shared property

USING POSTMAN FLOWS

USECASE:
Product enrichment in a web shop.

We receive a dataset of updated data. (Sample)
[
{
“Id”: “1”,
“GTIN”: “1GTIN”,
“Color”: “Blue”
},
{
“Id”: “2”,
“GTIN”: “2GTIN”,
“Color”: “Green”
},
{
“Id”: “3”,
“GTIN”: “3GTIN”,
“Color”: “Magenta”
}
]

We have a list of currently existing products in the web shop.

[
{
“Id”: “1”,
“Material”: “Straw”,
“Grams”: 76
},
{
“Id”: “2”,
“Material”: “Straw”,
“Grams”: 72
},
{
“Id”: “22”,
“Material”: “Plastic”,
“Grams”: 32
},
{
“Id”: “3”,
“Material”: “Paper”,
“Grams”: 116
}
]

I would like to combine these lists by their common ID, and discard all un-matched items.

So that I end up with a list that looks like this:
[
{
“Id”: “1”,
“GTIN”: “1GTIN”,
“Color”: “Blue”,
“Material”: “Straw”,
“Grams”: 76
},
{
“Id”: “2”,
“GTIN”: “2GTIN”,
“Color”: “Green”,
“Material”: “Straw”,
“Grams”: 72
},
{
“Id”: “3”,
“GTIN”: “3GTIN”,
“Color”: “Magenta”,
“Material”: “Paper”,
“Grams”: 116
}
]

Thank you for helping out!

Hi @madshundhammerfixit

You can try this:

$map($map(list1, fn ($v) {
    $map(list2, fn ($v2) {
        $v.Id = $v2.Id ? {"Id": $v.Id, "GTIN": $v.GTIN, "Color": $v.Color, "Materials": $v2.Materials, "Grams": $v2.Grams } : null
    }) 
}), fn ($f) {
    $merge($filter($f, fn ($d) {
        $exists($d)
    }))
})

What this FQL does is use $map to go through every element of the first list, and compare the Id with every Id element of the second list and return the structured data like you specified only if the Id matches, otherwise return null. Then go through the newly returned list of lists and $filter our nulls and then $merge those into an object

I used your test data and got the desired results. We are adding an easier way to do this in the near future :slightly_smiling_face:

Thank you, i will translate this for my actual data-structure. I am curious as to how this will perform on bigger data-sets.

This future functionality, are we talking “Join block”? Or new functions in FQL?

If the join block option is the case. Getting 3 outputs would be nice!
Successfully joined, Unmatched array 1 and unmatched array 2.

I realize you probably cannot give an outright answer, but I would be remiss if I didn’t mention it :slight_smile:

Also, I’ve noticed whenever you solve an issue, you consistently make sure to explain the solution. Good work!

@madshundhammerfixit

That’s all the details I can share for now, but thank you for your feedback, i’ll share it with the team for future ideas for Flows.

I will come back and update this thread once said alternative is available though :wink:

I try my best to explain the solutions so that others who may choose not to comment, but run into the same issue can re-use the code for their solutions that might be slightly different and help build some community knowledge.

Hey, can I use this technique to create a new list group by a specific property? Ex: if I follow your example. how can I get a new list group by “materials” property? the result will be something like this:

[
{
“materials”: “paper”,
“items”: […]
},
{
“materials”: “plastic”,
“items”: […]
},
]

Adding TypeScript solution below that will produce the same result:

interface Item {
    Id: string;
    GTIN?: string;
    Color?: string;
    Material?: string;
    Grams?: number;
}

function joinLists(list1: Item[], list2: Item[]): Item[] {
    const joinedList: Item[] = [];

    list1.forEach(item1 => {
        const matchingItem = list2.find(item2 => item2.Id === item1.Id);
        if (matchingItem) {
            joinedList.push({
                ...item1,
                Material: matchingItem.Material,
                Grams: matchingItem.Grams
            });
        } else {
            joinedList.push(item1);
        }
    });

    return joinedList;
}
joinLists(list1,list2);

@supply-candidate-367

To achieve what you’re asking for you can use the following TypeScript:

interface Item {
    Id: string;
    GTIN?: string;
    Color?: string;
    Material?: string;
    Grams?: number;
}

interface GroupedItem {
    material: string;
    items: Item[];
}

function groupByMaterial(list1: Item[], list2: Item[]): GroupedItem[] {
    const groupedItemsMap: Map<string, Item[]> = new Map();

    // Group items from list2 by Material
    list2.forEach(item => {
        if (item.Material) {
            const material = item.Material.toLowerCase();
            if (!groupedItemsMap.has(material)) {
                groupedItemsMap.set(material, []);
            }
            groupedItemsMap.get(material)?.push(item);
        }
    });

    // Group items from list1 by Material and merge with list2
    list1.forEach(item => {
        const material = item.Material?.toLowerCase();
        if (material && groupedItemsMap.has(material)) {
            groupedItemsMap.get(material)?.push(item);
        } else if (material) {
            groupedItemsMap.set(material, [item]);
        }
    });

    // Convert Map to array of GroupedItem objects
    const groupedItems: GroupedItem[] = [];
    groupedItemsMap.forEach((items, material) => {
        groupedItems.push({ material: material, items: items });
    });

    return groupedItems;
}
groupByMaterial(list1,list2);