Return elements of a JSON object as an array

Question

I have an XML file that is converted to a JSON object by a TRANSFORM MESSAGE:

%dw 2.0
output application/json
---
payload

The resulting JSON object has a format of:

{
  "Items": {
    "Item": {
      "ItemId": "123",
      "OrganizationId": "456",
      "OrganizationCode": "ABC",

where there is one "Items" and 112 "Item". What I want is to return an JSON array contains all of the ItemIds. I am attempting to use TRANSFORM MESSAGE within a FOREACH. The FOREACH has 'payload' in the collection field and TRANSFORM MESSAGE has:

%dw 2.0
output application/json
---
myItems: payload.items.*item map (item, index) -> {
    "myItemId" : item.ItemId
}

However, it always returns everything, the entire JSON object. I can't figure out if my FOREACH is wrong or if my TRANSFORM MESSAGE is wrong, but it always returns the entire JSON object.

---

An example of the incoming JSON would be:

{
  "Items": {
    "Item": {
      "ItemId": "8041",
      "OrganizationId": "12",
      "OrganizationCode": "ABC",
    },
    "Item": {
      "ItemId": "8050",
      "OrganizationId": "12",
      "OrganizationCode": "ABC",
    },
    "Item": {
      "ItemId": "3801",
      "OrganizationId": "12",
      "OrganizationCode": "ABC",
    }
  }
}

The output should be: ["8041", "8050", "3801"]. Parse the Item elements, extract the ItemID value and create a JSON array.

Answer 1

Your script works nearly well. Allow me to mention that the first transformation to convert from XML to JSON is counterproductive. The next transformation will have to parse JSON to emit JSON. It is better to do it in one step, from XML to JSON. If you need the intermediate result for some other processing, it is better to convert to Java (application/java), process, then when the final output is needed, then convert to the final JSON format. Processing intermediate steps in XML or JSON just consumes more resources.

Based on that I used the following XML input:

<Items>
    <Item>
        <ItemId>8041</ItemId>
        <OrganizationId>12</OrganizationId>
        <OrganizationCode>ABC</OrganizationCode>
    </Item>
    <Item>
        <ItemId>8050</ItemId>
        <OrganizationId>12</OrganizationId>
        <OrganizationCode>ABC</OrganizationCode>
    </Item>
    <Item>
        <ItemId>3801</ItemId>
        <OrganizationId>12</OrganizationId>
        <OrganizationCode>ABC</OrganizationCode>
    </Item>    
</Items>    

Script:

%dw 2.0
output application/json
---
payload.Items.*Item map (item, index) -> item.ItemId

Output:

[
  "8041",
  "8050",
  "3801"
]

The same script will work with your JSON input too.