arrays - 在逻辑应用中处理复杂 JSON 数据的正确/惯用方式是什么？
问题描述
我正在使用 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
}
]
}
]
}
}
}
第一个块描述列标题，然后是另一个块，其中每一行都定义为字段数组。我的目标是将其转换为以下（恕我直言）更常用的格式：
{
{
"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 元素并存储它。
在处理完所有行之后，我执行了一些更多的处理，通过反复试验得出了一些可以与 Create HTML Table 函数一起使用的东西。
虽然这种方法有效，但它似乎太复杂了。但是，我找不到另一种可行的方法。我是 Logic Apps 的新手，但不是编程新手。谁能提供更好/更惯用的方法来实现这一目标？还是这种类型的处理最好外包给某个职能部门？
PS 逻辑应用程序将在小数据集（两位数）上运行，所以我不太担心性能，但可能更惯用的解决方案会表现得更好。
解决方案
您可以按照文档的建议使用Liquid 模板来定义复杂的 json 转换：
对于包含迭代、控制流和变量等元素的高级且复杂的 JSON 到 JSON 转换，请使用 Liquid 开源模板语言创建和使用描述这些转换的模板。
创建液体模板的一种简单方法是使用带有一些插件的Visual Studio Code。
主要好处是使用逻辑应用设计器以这种方式创作转换要容易得多。此外，转换逻辑与业务逻辑分离。
您还可以使用 Liquid 模板将 JSON 转换为文本，因此可以选择直接转换为 html。
顺便说一句，您输入的 json 似乎无效，所以我无法提供一个简单的示例。
推荐阅读
- botframework - 在 Teams 中使用 azure bot 服务：如何将文件发送到 bot
- java - 编辑 jafavFx 表后更新数据库表
- qt - Qt QUdpSocket 在发送数据时触发 readyRead()
- gitlab - 用于 gitlab CI 的 REST API？
- python-3.x - 如何检查列表中所有可能的数字组合？
- javascript - 追踪涉及 Meteor 和多个 DDP 连接的内存泄漏
- python - 是否可以从 __init__.py 扩展一个类
- elasticsearch - 通过 kubernetes-ingress 流利到 elasticsearch
- flutter - 使用 Flutter 在 RxDart 中等待 observable 完成
- python - lmfit - 最小化器不接受 scipy 最小化器关键字参数