Apache Hop 没有丰富的 json 处理功能,因此我将数据推送到 postgres 来完成这项工作
对于深度嵌套的(下面的子集示例)json blob,如何展平然后存储结果?我将用 Hop 检索它并继续处理。最终的输出是csv。
就像是
更新 atable set flat_json = flatten(nested_json) where id = 1;
jq 的这个片段做到了,pg 相当于什么?
echo '[{"value": {
"id":"1",
"desc":"widget",
"colours":[{
"hue":"red",
"tint":"light"
},{
"hue":"green",
"tint":"light"
},{
"hue":"blue",
"tint":"dark"
}],
"isActive":true
}}]' | jq '[.[] |
[leaf_paths as $path | {"key": $path | join("_"), "value": getpath($path)}]
| from_entries]
'
result
[
{
"value_id": "1",
"value_desc": "widget",
"value_colours_0_hue": "red",
"value_colours_0_tint": "light",
"value_colours_1_hue": "green",
"value_colours_1_tint": "light",
"value_colours_2_hue": "blue",
"value_colours_2_tint": "dark",
"value_isActive": true
}
]
如果这是一个不优雅的想法,是否有一种简洁的方法将 json 推送到自动创建的列?(大约有 200 个)或者,将嵌套的 json 推送到由自动生成的外键链接的表集合(小部件、颜色等)?真实数据的嵌套比示例更深。
至于PG等价的,就是这样的SQL:
上面的 SQL 应该适用于任意 JSON,在本例中它返回以下内容:
需要递归SQL,因为JSON对象和数组需要递归解包。由于递归引用
json_tree
不能在查询中出现两次,因此我们在这一步中采取了一些技巧,使用两个左连接(其中只有一个与给定行匹配),并case ... when
确保json_each()
不会json_array_elements()
收到不匹配的参数类型,这会导致错误。(这不太优雅,我想知道是否有更好的方法。)SQL 将构造一个 JSON 路径作为数组,
array_to_string()
最终生成一个准列名称。(我通过使用 切片数组来省略路径的第一个元素json_path[2:]
,因为示例 JSON 中的顶级元素是一个数组,因此严格来说,正确的路径键将是0_value_id
、0_value_desc
等。)所有 JSON 路径都会生成,但我们只对叶节点感兴趣,而且 - 因为我们知道数组和对象不能是叶节点 - 我们最终需要排除它们。
至于第二个问题 - 也就是说,如果我理解正确的话,这是将任意 JSON 数据传播到(大概)匹配的数据库模式的最佳方法 - 我的猜测几乎肯定不是,尽管: