Need help in constructing a JSON payload in DW 2.0

Question

I have the below keys/values stored in Object Store. For example 'Employee_Number' is the key and below is the value/object associated for this key.

{
        employeeMetaColumn = Employee_Number,
        employeeMetaText = Employee Number,
        employeeMetaType = SECT,
        employeeMetaId = 18
}

Likewise I have many such key and value pairs as shown in the payload below (when I do Retrieve All object Store operation I get the below payload # 1). As we can see its not JSON.

**

Payload # 1 from Object Store.

**

{
    Employee_Number = {
        employeeMetaColumn = Employee_Number,
        employeeMetaText = Employee Number,
        employeeMetaType = SECT,
        employeeMetaId = 18
    }, Country_of_Birth = {
        employeeMetaColumn = Country_of_Birth,
        employeeMetaText = Country of Birth,
        employeeMetaType = SLD,
        employeeMetaId = 19
    }
}

I have below payload # 2 which I need to use for constructing the final payload # 3. Here Employee_Number maps to the key in the object Store ( for example 'Employee_Number' as shown in the above payload). So I want to use this key to fetch the value from object Store.

**

Payload # 2

**

{
  "employee": [
    {
      "Employee_Number": "123456",
      "Country_of_Birth": "USA",
    }
  ]
}

Now I need to construct the below Payload # 3

**

Payload # 3

**

{
"parameters": [{
        "employeeParamValue": [
            "123456"
        ],
        "employeeMetaColumn": "Employee_Number",
        "employeeMetaText": "Employee Number",
        "employeeMetaType": "SECT",
        "employeeMetaId": 18,
        
    },
    {
        "employeeParamValue": [
            "USA"
        ],
        "employeeMetaColumn": "Country_of_Birth",
        "employeeMetaText": "Country of Birth",
        "employeeMetaType": "SLD"
        "employeeMetaId": 19,
    }
]
}

note: employeeParamValue is an array eventhough it just has one value.

Answer 1

Try this

%dw 2.0
var lookup = {
    "Employee_Number": {
        "employeeMetaColumn": "Employee_Number",
        "employeeMetaText": "Employee Number",
        "employeeMetaType": "SECT",
        "employeeMetaId": 18
    }, 
    "Country_of_Birth": {
        "employeeMetaColumn": "Country_of_Birth",
        "employeeMetaText": "Country of Birth",
        "employeeMetaType": "SLD",
        "employeeMetaId": 19
    }
}
var in = {
  "employee": [
    {
      "Employee_Number": "123456",
      "Country_of_Birth": "USA",
    }
  ]
}
output application/json
---
parameters: in.employee[0]
    pluck ((value, key, index) -> 
        {
            "employeeParamValue":[value], (lookup[key])
        }
    )

Output

{
  "parameters": [
    {
      "employeeParamValue": [
        "123456"
      ],
      "employeeMetaColumn": "Employee_Number",
      "employeeMetaText": "Employee Number",
      "employeeMetaType": "SECT",
      "employeeMetaId": 18
    },
    {
      "employeeParamValue": [
        "USA"
      ],
      "employeeMetaColumn": "Country_of_Birth",
      "employeeMetaText": "Country of Birth",
      "employeeMetaType": "SLD",
      "employeeMetaId": 19
    }
  ]
}