I have a dataset that is an array of objects, and each object is another object with an array inside of it. I am trying to flatten everything to a single array of the innermost objects, without the keys of the middle layer objects. I have tried to use the pluck functionality, but I cannot seem to get to what I am after. Example below.

Input:

[
  {
    "1234": [
      {
        "store": "07",
        "category": "1234",
        "account": "987"
      },
      {
        "store": "07",
        "category": "1234",
        "account": "555"
      },
      {
        "store": "07",
        "category": "1234",
        "account": "555"
      }
    ]
  },
  {
    "567": [
      {
        "store": "07",
        "category": "567",
        "account": "987"
      },
      {
        "store": "07",
        "category": "567",
        "account": "555"
      },
      {
        "store": "07",
        "category": "567",
        "account": "555"
      }
    ]
  }
]

Output:

[
      {
        "store": "07",
        "category": "1234",
        "account": "987"
      },
      {
        "store": "07",
        "category": "1234",
        "account": "555"
      },
      {
        "store": "07",
        "category": "1234",
        "account": "555"
      },
      {
        "store": "07",
        "category": "567",
        "account": "987"
      },
      {
        "store": "07",
        "category": "567",
        "account": "555"
      },
      {
        "store": "07",
        "category": "567",
        "account": "555"
      }
]

CodePudding user response：

1. You need to iterate using map.
2. Convert object with key to array using pluck.
3. Covnert Nested array to 2d array using flatten.
4. Convert 2d array to 1d array using Reduce

DW

%dw 2.0
output application/json
---
flatten(payload map ($ pluck $)) reduce ($$  $)

Output

[
  {
    "store": "07",
    "category": "1234",
    "account": "987"
  },
  {
    "store": "07",
    "category": "1234",
    "account": "555"
  },
  {
    "store": "07",
    "category": "1234",
    "account": "555"
  },
  {
    "store": "07",
    "category": "567",
    "account": "987"
  },
  {
    "store": "07",
    "category": "567",
    "account": "555"
  },
  {
    "store": "07",
    "category": "567",
    "account": "555"
  }
]

CodePudding user response：

Below is one way of doing it. May be there is better way than this.

%dw 2.0
output application/json
---
flatten(payload map ($ pluck $)) reduce ((val, acc) -> acc    val)