JSONata array to array manipulation with mapping

Question

I need to transform array to array with some extra logic:

Map field name in array if it exists in the mapping object, if not process it as it is in the source
Sum up values of objects with the same name
Remove objects with zero value

for example here is the source json:

{
  "additive": [
      {
        "name": "field-1",
        "volume": "10"
      },
      {
        "name": "field-2",
        "volume": "10"
      },
      {
        "name": "field-3",
        "volume": "0"
      },
      {
        "name": "field-4",
        "volume": "5"
      }
    ]
}

object with mapping config(field-1 and field-2 is mapped to the same value):

{
  "field-1": "field-1-mapped",
  "field-2": "field-1-mapped",
  "field-3": "field-3-mapped"
}

and this is the result that I need to have

{
  "chemicals": [
    {
      "name": "field-1-mapped",
      "value": 20 
    },
    {
      "name": "field-4",
      "value": 5
    }
  ]
}

as you can see field-1 and field-2 is mapped to field-1-mapped so the values are summed up, field-3 has 0 value so it is removed and field-4 is passed as it is because it's missing in the mapping.

so my question is: is it possible to make it with JSONata?

I have tried to make it work but I stuck with this lookup function that doesn't return default value when name is missing in mapping:

{
"chemicals":  additive @ $additive #$.{
                    "name": $res := $lookup({
                          "field-1": "field-1-mapped",
                          "field-2": "field-1-mapped",
                          "field-3": "field-3-mapped"
                    }, $additive.name)[ $res ? $res : $additive.name],
                    "amount": $number($additive.volume),
                } [amount>0] 
}

Andrew Coleman · Accepted Answer · 2023-01-18 15:56:30Z

1

Probably easiest to break it down into steps as follows:

(
    /* define lookup table */
    $table := {
        "field-1": "field-1-mapped",
        "field-2": "field-1-mapped",
        "field-3": "field-3-mapped"
    };

    /* substitute the name; if it's not in the table, just use the name */
    $mapped := additive.{
        "name": [$lookup($table, name), name][0],
        "volume": $number(volume)
    };

    /* group by name, and aggregate the volumes */
    $grouped := $mapped[volume > 0]{name: $sum(volume)};
    
    /* convert back to array */
    {
        "chemicals": $each($grouped, function($v, $n) {{
            "name": $n,
            "volume": $v
        }})
    }
)

See https://try.jsonata.org/0BWeRcRoZ

answered Jan 18, 2023 at 15:56

Andrew Coleman

1,61711 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSONata array to array manipulation with mapping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related