How to write JSONata query to remove duplicate values from some tags in JSON and to get unique value of each object using jsaonata

Question

Source of JSON entry is like below. I want to get unique value from keys Type* and Category* for each "Products" object

{
    "data" : {
        "Products" : [
        {
            "Type" : "Apple",
            "Type_2" : "Lemon",
            "Type_3" : "Apple",
            "Category" : "Pear",
            "Category_2" : "Peanut"
            "Category_3" : "Lemon",
            "Area" : "Lot1",
            "Key" : "Section1"          
        },
        {
            "Type" : "Pear",
            "Type_2" : "Grape",
            "Type_3" : "Lemon",
            "Category" : "Lemon",
            "Category_2" : "Pear"
            "Category_3" : "Walnut",
            "Area" : "Lot2",
            "Key" : "Section2"
        }
        ]
    }       
}

Expected results are

{
    "Items" : [
    {
        "Collection" : "Apple",
        "Area" : "Lot1",
        "Key" : "Section1"
    },
    {
        "Collection" : "Lemon",
        "Area" : "Lot1",
        "Key" : "Section1"
    },
    {
        "Collection" : "Pear",
        "Area" : "Lot1",
        "Key" : "Section1"
    },
    {
        "Collection" : "Peanut",
        "Area" : "Lot1",
        "Key" : "Section1"
    },
    {
        "Collection" : "Pear",
        "Area" : "Lot2",
        "Key" : "Section2"
    },
    {
        "Collection" : "Grape",
        "Area" : "Lot2",
        "Key" : "Section2"
    },
    {
        "Collection" : "Lemon",
        "Area" : "Lot2",
        "Key" : "Section2"
    },
    {
        "Collection" : "Apple",
        "Area" : "Lot2",
        "Key" : "Walnut"
    }
    ]
}

Unique value from Type* and Category* of each source object is shown for value of Collection tag.

Can anyone help me to write jsonata to get expected results?

Answer 1

If I understood you correctly, you need to iterate over all possible values of Type and Category properties. To achieve that, you can use a combination of $keys and $lookup functions with a filtering operation in-between, like this:

{
  "Items": data.Products.(
    $product := $;
    $typeAndCategoryKeys := $keys($product)[$contains($, "Category") or $contains($, "Type")];
    $typeAndCategoryValues := $typeAndCategoryKeys.($lookup($product, $)) ~> $distinct;

    $typeAndCategoryValues.{
      "Collection": $,
      "Area": $product.Area,
      "Key": $product.Key
    }
  )
}

Check it out on the JSONata Playground