Convert array of json into single json with inner array

Question

I'm getting a message from a system in json format and want to perform a transformation in order to get below message

Source message:

    [
{
    "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": "2019-01-03T22:00:00",
    "BATCH_ID": 1812,
    "STORENUMBER": 1197,
    "PROCESS_STATUS": "P"
},
{
   "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": "2019-01-04T18:00:00",
    "BATCH_ID": 1812,
    "STORENUMBER": 1197,
    "PROCESS_STATUS": "P"
},
{
    "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": "2019-01-03T22:00:00",
    "BATCH_ID": 1314,
    "STORENUMBER": 1198,
    "PROCESS_STATUS": "P"
},
{
    "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": "2019-01-04T18:00:00",
    "BATCH_ID": 1314,
    "STORENUMBER": 1198,
    "PROCESS_STATUS": "P"
}]

Expected Output

    [
{
    "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": ["2019-01-03T22:00:00","2019-01-04T18:00:00"],
    "BATCH_ID": 1812,
    "STORENUMBER": 1197,
    "PROCESS_STATUS": "P"
},
{
   "OPERATING_COMPANY": "xx",
    "STORE_CLOSING_TIME": ["2019-01-03T22:00:00","2019-01-04T18:00:00"],
    "BATCH_ID": 1812,
    "STORENUMBER": 1198,
    "PROCESS_STATUS": "P"
}]

I'm new to dataweave and tried using map but not able to understand how to achieve result.

Please post what you have tried so we may assist in fixing it. — Mark Schultheiss

Anirban Sen Chowdhary Anirban Sen Chowdhary · Accepted Answer · 2019-01-03T22:36:31

Try the following in dataweave:

%dw 1.0
%output application/json
%var time=(payload map $.STORE_CLOSING_TIME)  distinctBy $.STORE_CLOSING_TIME 
---
(payload map {

 "OPERATING_COMPANY": $.OPERATING_COMPANY,
 "STORE_CLOSING_TIME": time,
 "BATCH_ID": $.BATCH_ID,
 "STORENUMBER": $.STORENUMBER,
 "PROCESS_STATUS": $.PROCESS_STATUS 
    }) distinctBy $.STORENUMBER

output:

[
    {
        "OPERATING_COMPANY": "xx",
        "STORE_CLOSING_TIME": [
            "2019-01-03T22:00:00",
            "2019-01-04T18:00:00"
        ],
        "BATCH_ID": 1812,
        "STORENUMBER": 1197,
        "PROCESS_STATUS": "P"
    },
    {
        "OPERATING_COMPANY": "xx",
        "STORE_CLOSING_TIME": [
            "2019-01-03T22:00:00",
            "2019-01-04T18:00:00"
        ],
        "BATCH_ID": 1314,
        "STORENUMBER": 1198,
        "PROCESS_STATUS": "P"
    }
]

Convert array of json into single json with inner array

1 Answers