如何将JSON数组展平为csv文件 [英] How do I flatten a JSON array to a csv file

问题描述

我有一个JSON格式的传入有效负载，我将其中的一些对象输出到CSV文件.有效负载还具有一个数组:

I have an incoming payload in JSON format where I am outputting some of the objects to a CSV file. The payload also has one array:

      "Chargebacks": [
        {
          "CostCenterCode": "123ABC",
          "AllocationPercentage": 100
        },
        {
            "CostCenterCode": "456DEF",
            "AllocationPercentage": 100
        }
      ]

我需要CSV文件包含:

I need the CSV file to contain:

<other headers from the objects>,Cost Center Code 1, Allocation Percentage 1, Cost Center Code 2, Allocation Percentage 2
<other object values>,123ABC,100,456DEF,100

我的第一个尝试是创建两个变量以保存标头列表和值列表:

My first attempt was to create two variables to hold the list of headers and list of values:

%dw 2.0
output application/csv
var x = payload.Item.CatalogAttributes.Chargebacks map (chargeBack, index) -> 
{
    "header": "Cost Center Code " ++ index+1 ++ ", Allocation Percentage "++ index+1,
    "costCenterCode": chargeBack.CostCenterCode ++ "," ++ chargeBack.AllocationPercentage,
}
var foo = x.*header joinBy ','
var bar = x.*costCenterCode joinBy ','
---

并将它们添加到文件末尾:

and add them to the end of the file:

foo: bar

，并且可以正常工作.我得到值"foo"在标题的末尾，在 123ABC \，100 \，456DEF \，100 的末尾.如何获取foo的实际值并从值中删除斜杠?

and it kind of works. I get the value "foo" at the end of the headers and 123ABC\,100\,456DEF\,100 at the end of the values. How do I get the actual value of foo and remove the slashes from the values?

推荐答案

我要在我的答案中假设您可能不提前知道有多少笔退款项目.该数据织法:

I'm going to assume in my answer you might not know ahead of time how many chargeback items there are. This dataweave:

%dw 2.0
output application/csv
---
payload map {
    ($ - "Chargebacks"),
    ($.Chargebacks map {
        ("CostCenterCode_$($$)": $.CostCenterCode),
        ("AllocationPercentage_$($$)": $.AllocationPercentage)
    })
}

使用此示例输入:

[
    {
        "field1": "someValue",
        "field2": "someValue",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            }
        ]
    },
    {
        "field1": "someValue2",
        "field2": "someValue2",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC2",
                "AllocationPercentage": 200
            },
            {
                "CostCenterCode": "456DEF2",
                "AllocationPercentage": 200
            }
        ]
    }
]

产生此csv:

field1,field2,CostCenterCode_0,AllocationPercentage_0,CostCenterCode_1,AllocationPercentage_1
someValue,someValue,123ABC,100,456DEF,100
someValue2,someValue2,123ABC2,200,456DEF2,200

通过将地图包装在( ... )中，我们基本上是在告诉它获取结果数组并将其展平到顶级对象中.如果您熟悉的话，这与javascript中的传播运算符非常相似. $ 和 $$ 是该函数的简写.例如，如果您具有这样的功能: fun someFun(left，fn:(item，index)-> Any)，则可以使用以下模式调用它 payload someFun ...，其中有效负载变为参数 left ，然后右侧变为函数；传递给函数的每个参数都将成为一个 $ ，其中 $ s的数量是该参数的位置.有道理?请注意，这种调用函数的模式不仅限于采用一种函数的模式.例如，您可以创建一个像这样的函数: fun add(left，right)= left + right 并以此方式调用它 1 add 2 .这仅在使用 fun 关键字以及恰好具有两个参数时有效.

By wrapping our map in the (...) we are basically telling it to take the resulting array and flatten it into the top level object. This is very similar to the spread operator in javascript if you're familiar. The $ and $$ are shorthand for the function. Eg, if you have a function like this: fun someFun(left, fn: (item, index) -> Any), you can call it using this pattern payload someFun ..., where payload becomes the parameter left, and then the right hand side becomes the function; each parameter passed into the function becomes a $, where the number of $s is the position of the parameter. Make sense? Note that this pattern of calling functions isn't limited to one that takes a function. Eg, you could create a function like this: fun add(left, right) = left + right and call it this way 1 add 2. This only works when using the fun keyword, and when you have exactly two parameters.

如果您可能要使用不规则的尺寸(例如:某些尺寸可能比其他尺寸更大)，并且需要为较小的元素添加空白条目，则需要提前确定最大尺寸，然后执行类似的操作这个:

If you're going to have potentially irregular sizes (ie: some might have more than others) and need to have the blank entries for smaller elements, you'll need to determine the max size ahead of time and do something like this:

%dw 2.0
output application/csv
var maxSize = max(payload map sizeOf($.Chargebacks))
---
payload map (row) -> {
    (row - "Chargebacks"),
    ((1 to maxSize) map {
        ("CostCenterCode_$($$)"): row.Chargebacks[$$].CostCenterCode,
        ("AllocationPercentage_$($$)"): row.Chargebacks[$$].AllocationPercentage
    })
}

这将映射这样的输入:

[
    {
        "field1": "someValue",
        "field2": "someValue",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            },
            {
                "CostCenterCode": "456DEF",
                "AllocationPercentage": 100
            }
        ]
    },
    {
        "field1": "someValue2",
        "field2": "someValue2",
        "Chargebacks": [
            {
                "CostCenterCode": "123ABC2",
                "AllocationPercentage": 200
            },
            {
                "CostCenterCode": "456DEF2",
                "AllocationPercentage": 200
            }
        ]
    }
]

对此:

field1,field2,CostCenterCode_0,AllocationPercentage_0,CostCenterCode_1,AllocationPercentage_1,CostCenterCode_2,AllocationPercentage_2
someValue,someValue,123ABC,100,456DEF,100,456DEF,100
someValue2,someValue2,123ABC2,200,456DEF2,200,,

这篇关于如何将JSON数组展平为csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！