arrays - 在逻辑应用程序中处理复杂 JSON 数据的正确/惯用方法是什么？

Question

我正在使用 JSON API 调用从 Web 服务读取数据。它以(恕我直言)一种不寻常的格式返回数据:

{
    "statusCode": 200,
    "headers": {
        "Transfer-Encoding": "chunked",
        "Content-Type": "application/json; charset=UTF-8",
    },
    "body": {
        "columns": {
            "column": [
                {
                    "id": -1,
                    "val": null,
                    "name": "COLUMNA",
                    "caption": "Column A",
                    "value": null
                },
                {
                    "id": 106009714,
                    "val": null,
                    "name": "COLUMNB",
                    "caption": "Column B",
                    "value": null
                },
                {
                    "id": 106010949,
                    "val": null,
                    "name": "COLUMNC",
                    "caption": "Column C",
                    "value": null
                },
                {
                    "id": 106009719,
                    "val": null,
                    "name": "COLUMND",
                    "caption": "Column D",
                    "value": null
                },
        "records": {
            "record": [
                {
                    "field": [
                        {
                            "id": -1,
                            "val": "390011768",
                            "name": "COLUMNA",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009714,
                            "val": "Lorem ipsum",
                            "name": "COLUMNB",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106010949,
                            "val": "Nulla mauris ante",
                            "name": "COLUMNC",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009719,
                            "val": "15-01-2019 12:46",
                            "name": "COLUMND",
                            "caption": null,
                            "value": null
                        }
                    ]
                },
                {
                    "field": [
                        {
                            "id": -1,
                            "val": "390012438",
                            "name": "COLUMNA",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009714,
                            "val": "Vestibulum ligula",
                            "name": "COLUMNB",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106010949,
                            "val": "Nulla elit orci",
                            "name": "COLUMNC",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009719,
                            "val": "27-03-2019 14:17",
                            "name": "COLUMND",
                            "caption": null,
                            "value": null
                        }
                    ]
                },
                {
                    "field": [
                        {
                            "id": -1,
                            "val": "390013343",
                            "name": "COLUMNA",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009714,
                            "val": "Nunc magna risus",
                            "name": "COLUMNB",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106010949,
                            "val": "Vivamus rutrum",
                            "name": "COLUMNC",
                            "caption": null,
                            "value": null
                        },
                        {
                            "id": 106009719,
                            "val": "23-07-2019 13:17",
                            "name": "COLUMND",
                            "caption": null,
                            "value": null
                        }
                    ]
                }
            ]
        }
    }
}

第一个 block 描述列标题，后面是另一个 block ，其中每行定义为字段数组。我的目标是将其转换为以下(恕我直言)更常用的格式:

{
    {
        "Column A":"390011768",
        "Column B":"Lorem ipsum",
        "Column C":"Nulla mauris ante",
        "Column D":"27-03-2019 14:17"
    },
    etc
}

我已经想出了一个解决方案，但感觉很困惑/错误，并且涉及字符串对象和数组之间的大量转换。其代码如下:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "CreateJSON": {
                "inputs": {
                    "body": {
                        "columns": {
                            "column": [
                                {
                                    "caption": "Column A",
                                    "id": -1,
                                    "name": "COLUMNA",
                                    "val": null,
                                    "value": null
                                },
                                {
                                    "caption": "Column B",
                                    "id": 106009714,
                                    "name": "COLUMNB",
                                    "val": null,
                                    "value": null
                                },
                                {
                                    "caption": "Column C",
                                    "id": 106010949,
                                    "name": "COLUMNC",
                                    "val": null,
                                    "value": null
                                },
                                {
                                    "caption": "Column D",
                                    "id": 106009719,
                                    "name": "COLUMND",
                                    "val": null,
                                    "value": null
                                }
                            ]
                        },
                        "records": {
                            "record": [
                                {
                                    "field": [
                                        {
                                            "caption": null,
                                            "id": -1,
                                            "name": "COLUMNA",
                                            "val": "390011768",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009714,
                                            "name": "COLUMNB",
                                            "val": "Lorem ipsum",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106010949,
                                            "name": "COLUMNC",
                                            "val": "Nulla mauris ante",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009719,
                                            "name": "COLUMND",
                                            "val": "15-01-2019 12:46",
                                            "value": null
                                        }
                                    ]
                                },
                                {
                                    "field": [
                                        {
                                            "caption": null,
                                            "id": -1,
                                            "name": "COLUMNA",
                                            "val": "390012438",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009714,
                                            "name": "COLUMNB",
                                            "val": "Vestibulum ligula",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106010949,
                                            "name": "COLUMNC",
                                            "val": "Nulla elit orci",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009719,
                                            "name": "COLUMND",
                                            "val": "27-03-2019 14:17",
                                            "value": null
                                        }
                                    ]
                                },
                                {
                                    "field": [
                                        {
                                            "caption": null,
                                            "id": -1,
                                            "name": "COLUMNA",
                                            "val": "390013343",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009714,
                                            "name": "COLUMNB",
                                            "val": "Nunc magna risus",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106010949,
                                            "name": "COLUMNC",
                                            "val": "Vivamus rutrum",
                                            "value": null
                                        },
                                        {
                                            "caption": null,
                                            "id": 106009719,
                                            "name": "COLUMND",
                                            "val": "23-07-2019 13:17",
                                            "value": null
                                        }
                                    ]
                                }
                            ]
                        }
                    },
                    "headers": {
                        "Content-Type": "application/json; charset=UTF-8",
                        "Date": "Tue, 22 Sep 2020 17:26:18 GMT",
                        "Transfer-Encoding": "chunked"
                    },
                    "statusCode": 200
                },
                "runAfter": {},
                "type": "Compose"
            },
            "Create_HTML_table": {
                "inputs": {
                    "format": "HTML",
                    "from": "@variables('RecordArray')"
                },
                "runAfter": {
                    "ReconstituteArray": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "FlattenArray": {
                "inputs": {
                    "variables": [
                        {
                            "name": "ArrayString",
                            "type": "string",
                            "value": "@{replace(join(variables('RowAccumulator'), ','),'],[',',')}"
                        }
                    ]
                },
                "runAfter": {
                    "RowLoop": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "HeaderCollection": {
                "actions": {
                    "ColumnHeadings": {
                        "inputs": {
                            "caption": "@items('HeaderCollection')['caption']",
                            "name": "@items('HeaderCollection')['name']"
                        },
                        "runAfter": {},
                        "type": "Compose"
                    }
                },
                "foreach": "@outputs('CreateJSON')['body']['columns']['column']",
                "runAfter": {
                    "CreateJSON": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "InitFieldAccumulator": {
                "inputs": {
                    "variables": [
                        {
                            "name": "FieldAccumulator",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "InitRowString": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "InitRowAccumulator": {
                "inputs": {
                    "variables": [
                        {
                            "name": "RowAccumulator",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "InitFieldAccumulator": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "InitRowString": {
                "inputs": {
                    "variables": [
                        {
                            "name": "RowString",
                            "type": "string"
                        }
                    ]
                },
                "runAfter": {
                    "HeaderCollection": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "ReconstituteArray": {
                "inputs": {
                    "variables": [
                        {
                            "name": "RecordArray",
                            "type": "array",
                            "value": "@json(variables('ArrayString'))"
                        }
                    ]
                },
                "runAfter": {
                    "FlattenArray": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "RowLoop": {
                "actions": {
                    "FieldLoop": {
                        "actions": {
                            "Compose": {
                                "inputs": "@setProperty(item(), 'caption', body('MatchCaption')[0]['caption'])",
                                "runAfter": {
                                    "MatchCaption": [
                                        "Succeeded"
                                    ]
                                },
                                "type": "Compose"
                            },
                            "FieldAccumulator": {
                                "inputs": {
                                    "name": "FieldAccumulator",
                                    "value": "@outputs('Compose')"
                                },
                                "runAfter": {
                                    "Compose": [
                                        "Succeeded"
                                    ]
                                },
                                "type": "AppendToArrayVariable"
                            },
                            "MatchCaption": {
                                "inputs": {
                                    "from": "@outputs('ColumnHeadings')",
                                    "where": "@equals(item()['name'], items('FieldLoop')['name'])"
                                },
                                "runAfter": {},
                                "type": "Query"
                            }
                        },
                        "foreach": "@items('RowLoop')['field']",
                        "runAfter": {},
                        "runtimeConfiguration": {
                            "concurrency": {
                                "repetitions": 1
                            }
                        },
                        "type": "Foreach"
                    },
                    "FlattenRow": {
                        "inputs": {
                            "name": "RowString",
                            "value": "@{replace(string(body('Select')), '},{', ',')}"
                        },
                        "runAfter": {
                            "Select": [
                                "Succeeded"
                            ]
                        },
                        "type": "SetVariable"
                    },
                    "Reset_FieldAccumulator": {
                        "inputs": {
                            "name": "FieldAccumulator",
                            "value": []
                        },
                        "runAfter": {
                            "RowAccumulator": [
                                "Succeeded"
                            ]
                        },
                        "type": "SetVariable"
                    },
                    "RowAccumulator": {
                        "inputs": {
                            "name": "RowAccumulator",
                            "value": "@variables('RowString')"
                        },
                        "runAfter": {
                            "FlattenRow": [
                                "Succeeded"
                            ]
                        },
                        "type": "AppendToArrayVariable"
                    },
                    "Select": {
                        "inputs": {
                            "from": "@variables('FieldAccumulator')",
                            "select": {
                                "@{item()['caption']}": "@item()['val']"
                            }
                        },
                        "runAfter": {
                            "FieldLoop": [
                                "Succeeded"
                            ]
                        },
                        "type": "Select"
                    }
                },
                "foreach": "@outputs('CreateJSON')['body']['records']['record']",
                "runAfter": {
                    "InitRowAccumulator": [
                        "Succeeded"
                    ]
                },
                "runtimeConfiguration": {
                    "concurrency": {
                        "repetitions": 1
                    }
                },
                "type": "Foreach"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "Recurrence": {
                "recurrence": {
                    "frequency": "Day",
                    "interval": 1
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {}
}

首先，我创建一个 JSON 有效负载(模拟从 API 返回的内容)。然后我有一个循环，它创建一个包含列名称和标题的数组，稍后我将使用它来提供友好的列名称。

第二个循环迭代“记录”元素，这些元素本身就是数组。它使用 FilterArray 查找正确的标题，使用 Compose 创建 JSON 片段，然后将其存储到每个字段。

处理完一行的所有字段后，我使用 Select 来隔离我需要的值，然后将其转换为“正常”JSON 元素并存储它。

处理完所有行后，我会执行更多处理，通过反复试验来得出可与“创建 HTML 表”功能配合使用的内容。

虽然这种方法有效，但似乎太复杂了。但是我找不到另一种有效的方法。我是逻辑应用程序的新手，但不是编程。任何人都可以提供更好/更惯用的方法来实现这一目标吗？或者这种类型的处理最好外包给某个函数吗？

PS 逻辑应用程序将在小型数据集(两位数)上运行，因此我不太担心性能，但想必更惯用的解决方案会表现得更好。

Answer 1

您可以使用Liquid templates定义复杂的 json 转换，如 docs 的建议:

For advanced and complex JSON to JSON transformations that have elements such as iterations, control flows, and variables, create and use templates that describe these transformations by using the Liquid open-source template language.

创建液体模板的一种简单方法是使用 Visual Studio Code与 some plugins .

主要好处是，以这种方式创作转换比使用逻辑应用设计器要容易得多。此外，转换逻辑与业务逻辑是分离的。

您还可以使用 Liquid 模板将 JSON 转换为文本，因此直接转换为 html 是一种选择。

顺便说一句，您输入的 json 似乎无效，因此我无法提供简单的示例。