如何编写JSONata查询提取Products对象中Type*和Category*的唯一值

Query Statement #

{
  "Items": data.Products.(
    $area := Area;
    $key := Key;
    $filteredValues := $filter($$.keys(), $ ~> /^(Type|Category)/)
                       .($lookup($$, $))
                       ~> $unique();
    $filteredValues.{
      "Collection": $,
      "Area": $area,
      "Key": $key
    }
  )
}

Breakdown of the Query #

• Iterate through Products: Loop over each object in data.Products
• Cache Context Values: Store the current product's Area and Key in variables ($area, $key) for reuse
• Filter Relevant Keys: Use $filter with regex /^(Type|Category)/ to select only keys starting with "Type" or "Category"
• Extract and Deduplicate Values: Fetch values for the filtered keys, then apply $unique() to remove duplicates
• Map to Target Structure: Convert each deduplicated value into an object with Collection (the value itself), plus the cached Area and Key
• Combine Results: All generated objects are automatically flattened into a single array under the Items field

Note on Input JSON Syntax #

Your input JSON has missing commas that will cause parsing errors. Fix these lines:

• First product: "Category_2" : "Peanut" → add a comma at the end: "Category_2" : "Peanut",
• Second product: "Category_2" : "Pear" → add a comma at the end: "Category_2" : "Pear",

内容的提问来源于stack exchange，提问作者topto