How to group data separate JSONs into one JSON

Question

I have a stream of data that has the same header structure, with differing currencies. And I want to group them into single JSONs by individual currencies and store them in an object store. The issue I'm having is that the dataflow is asynchronous, and will need to be appended onto what is stored into the object store.

An example: Say at 12:00GMT I receive these two, separate json objects

1:

"prices": {
    header": {
        "currency": "EUR"
    },
    "price":{
        "productId":"0000A",
        "value":"60.00"
    }
}

2:

"prices":{
    header": {
        "currency": "GBP"
        },
    "price":{
        "productId":"000AA",
        "value":"20.00"
    }
}

I save them in an object store with the currency as the key. Then at 12:30GMT I get the next two values:

3:

"prices":{
    header": {
        "currency": "GBP"
        },
    "price":{
        "productId":"000BB",
        "value":"27.00"
    }
}

4:

"prices":{
    header": {
        "currency": "EUR"
        },
    "price":{
        "productId":"0000B",
        "value":"120.00"
    }
}

I want the Object store to look like this, with the currency as the key for each:

EUR:    "prices": {
        header": {
            "currency": "EUR"
            },
        "price":{
            "productId":"0000A",
            "amount":"60.00"
         },
        "price":{
            "productId":"0000B",
            "amount":"120.00"
        }
    }

GBP:    "prices":{
        header": {
            "currency": "GBP"
            },
        "price":{
            "productId":"000AA",
            "amount":"20.00"
        },
        "price":{
            "productId":"000BB",
            "amount":"27.00"
        }
    }

Is the value an array? or do you want the JSON with duplicate keys? — Harshank Bansal, Sep 05 '22 at 15:53
Note that the JSON objects are invalid JSON. A starting curly brace is missing and the header key misses its beginning double quote. — aled, Sep 05 '22 at 17:29
You probably want to change your output so that there are not duplicate "price" keys in the JSON object. https://stackoverflow.com/a/23195243/1649678 — Ryan Hoegg, Sep 07 '22 at 11:52

score 0 · Answer 1 · answered Sep 05 '22 at 17:27

You can use the update operator to replace the previous value of the field. To add the new key just concatenate the field from the new object to the old one, removing the extra header field.

%dw 2.0
output application/json
var oldValue={
        "prices": {
        "header": {
            "currency": "EUR"
        },
        "price":{
            "productId":"0000A",
            "value":"60.00"
        }
    }
}
var newValue={
    "prices": {
        "header": {
            "currency": "EUR"
            },
        "price":{
            "productId":"0000B",
            "value":"120.00"
        }
    }
}
---
oldValue update {
        case prices at .prices -> prices ++ newValue.prices - "header"
}

Output:

{
  "prices": {
    "header": {
      "currency": "EUR"
    },
    "price": {
      "productId": "0000A",
      "value": "60.00"
    },
    "price": {
      "productId": "0000B",
      "value": "120.00"
    }
  }
}

score 0 · Answer 2 · answered Sep 06 '22 at 13:42

Similar to Aled's answer, Used upsert operator (!) to handle null/empty case first time the object is retrieved from OS.

%dw 2.0
output application/json
var valueFromOS= {
        "prices": {
            "header": {
                "currency": "EUR"
            },
            "price":{
                "productId":"0000A",
                "value":"60.00"
            }
    }
}
var newValue={
    "prices": {
        "header": {
            "currency": "EUR"
            },
        "price":{
            "productId":"0000B",
            "value":"120.00"
        }
    }
}
---
(valueFromOS default {}) update {
        case prices at .prices! -> do {
            var header =  prices.header default newValue.prices.header default {}
            ---
            {
                header: header
            } 
            ++ ((prices default {}) - "header")
            ++ ((newValue.prices default {}) - "header")
        }
}

The header would not be null because it is the one that will be used to get the `valueFromOS`. Also how will you know to which object store value to merge the new payload if the header is null? All the null checks are redundant IMO — Harshank Bansal, Sep 06 '22 at 16:59

How to group data separate JSONs into one JSON

2 Answers2