jsondataweavemulesoftanypoint-studiomule4

Lookup a JSON array based on an ID to get additional fields using Dataweave in a Loop


How do you do find elements for a key in another json using dataweave?

I have to 2 JSON arrays; one which is standing lookup data and second which I am creating dynamically and I need to lookup the values based on a key from my lookup json.

For e.g. My lookup array looks like:

[{"id": 1, "shortname": "John", "fullname": "John Doe", "age" : 40,"designation": "Engineer"},
{"id": 2, "shortname": "Mary", "fullname": "Mary Jane","age" : 36,"designation": "Manager"}]

Now when I looping through my payload, I only get the id in it from my API Response and I need to add the shortname and fullname for the id passed to me in the final response. Something like below:

Input Payload is:

[{
  "id": 1,
  "project": "ABC",
  "rate": "150"
}
{
  "id": 2,
  "project": "ABC",
  "rate": "200"
}]

Output Payload required is:

[{
  "id": 1,
  "fullname": "John Doe"
  "shortName": "John"
  "project": "ABC",
  "rate": "150"
}
{
  "id": 2,
  "fullname": "Mary Jane"
  "shortName": "John"
  "project": "ABC",
  "rate": "200"
}]

The dataweave expression I am trying to write is:

payload map(item, index) -> {
 "id": item.id,
 "fullname": "Lookup from other array based on id",
 "shortname": "Lookup from other array based on id",
  "project": item.project,
  "rate": item.rate
}

How can I get the value of fullname and shortname from another array based on the id as I am modifying the response payload? I don't want all the data from my lookup Array rather only specific fields.


Solution

  • You could use one of the join functions from the Arrays module, like leftJoin(). The after joining do the mapping to the final expected output.

    In general don't think about transformations in terms of 'looping'. DataWeave is a functional language. Think on terms of mapping, filtering, etc.

    %dw 2.0
    output application/json
    import * from dw::core::Arrays
    var lookup=[{"id": 1, "shortname": "John", "fullname": "John Doe", "age" : 40,"designation": "Engineer"},
    {"id": 2, "shortname": "Mary", "fullname": "Mary Jane","age" : 36,"designation": "Manager"}]
    ---
    leftJoin(payload, lookup, (left) -> left.id, (right) -> right.id ) 
        map(item) -> {
            (item.l),
            "fullname": item.r.fullname,
            "shortname": item.r.shortname
        }
    

    Output (for the input shared):

    [
      {
        "id": 1,
        "project": "ABC",
        "rate": "150",
        "fullname": "John Doe",
        "shortname": "John"
      },
      {
        "id": 2,
        "project": "ABC",
        "rate": "200",
        "fullname": "Mary Jane",
        "shortname": "Mary"
      }
    ]