inner join between two "tables" using JOLT

Question

I'm trying to convert a json to json file using two object arrays and "join", this is the input file:

{
    "Employee": [
        {
            "id": "emp_1",
            "firstName": "Jose",
            "lastName": "Perez",
            "ssn": "ssn1",
            "depId": "dep_1"
        },
        {
            "id": "emp_2",
            "firstName": "Antonio",
            "lastName": "Ramirez",
            "ssn": "ssn2",  
            "depId": "dep_2"
        }
    ],
    "Department": [
        {
            "id": "dep_1",
            "description": "Instituto nacional de investigaciones nucleares (ININ)",
            "division": "Research"
        },
        {
            "id": "dep_2",
            "description": "Instituto Mexicano de Seguro Social (IMSS)",
            "division": "Healthcare"
        },
        {
            "id": "dep_3",
            "description": "Comision Nacional Bancaria y de Valores (CNBV)",
            "division": "Financial"
        }
    ]
}

This is the expected output:

{
    "Employee": [
        {
            "id": "emp_1",
            "firstName": "Jose",
            "lasttName": "Perez",
            "ssn": "ssn1",
            "department": "Instituto nacional de investigaciones nucleares (ININ)",
            "division": "Research"
        },
        {
            "id": "emp_2",
            "firstName": "Antonio",
            "lasttName": "Ramirez",
            "ssn": "ssn2",
            "department": "Instituto Mexicano de Seguro Social (IMSS)",
            "division": "Healthcare"
        }
    ]
}

I've been trying to do it, but is not getting mapped, what am I doing wrong? This is my spec:

[
    {
        "operation": "shift",
        "spec": {
            "Department": {
                "*": {
                    "@" : "Department.@id"
                }
            },
            "Employee" : "Employee"
        }

    },
    {
        "operation": "shift",
        "spec": {
            "Employee": {
                "*": {
                    "depId" : {
                        "*" : {
                            "@2" : {
                                "Department" : {
                                    "&4" : "test"
                                }    
                            }
                        }
                    }
                }
            }
        }

    }
]

Please I already spent a lot of time trying to solve it, does anyone has any idea of how to solve it using Jolt: https://github.com/bazaarvoice/jolt ?

Answer 1

Check this spec, make the id in department easier to reach and then compare the values,

[
  {
    "operation": "shift",
    "spec": {
      "Employee": "Employee",
      //make the dep id easier to compare
      "Department": {
        "*": {
          "@": "Department.@(0,id)"
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "Employee": {
        "*": {
          "depId": {
            "*": {
              "@(4,Department)": {
                // Compare values and move everything into the employee object
                "@3": "Employee.&",
                "@(&)": "Employee.&.department"
              }
            }
          }
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "Employee": {
        "*": {
          "@": "Employee[]"
        }
      }
    }
  }, {
    // Object cleansing
    "operation": "shift",
    "spec": {
      "Employee": {
        "*": {
          "id": "Employee[].id",
          "firstName": "Employee[&1].firstName",
          "lastName": "Employee[&1].lastName",
          "ssn": "Employee[&1].ssn",
          "department": {
            "description": "Employee[&2].department",
            "division": "Employee[&2].division"
          }
        }
      }
    }
  }
]