jq - 如何左连接和合并来自两个输入 JSON 文件的字段

MTS

我有两个要合并在一起的 JSON 文件,保留第一个文件中的所有记录,并根据匹配的键字段值合并第二个文件中的字段。

我正在使用jqLinux 上的命令行工具来执行此操作。我可以使用的最新版本jq是:jq-1.5-1-a5b5cbe

这是我的输入文件,以及我试图生成的 JSON:

输入文件#1: input1.json

[
  {
    "id": "item1",
    "join_id": "123",
    "field1": "val1",
    "field2": "val2"
  },
  {
    "id": "item2",
    "join_id": "123",   // Same join_id as above
    "field1": "val1",
    "field2": "val2"
  },
  {
    "id": "item3",
    "join_id": "456",
    "field1": "val3",
    "field2": "val4"
  },
  {
    "id": "item4",
    "join_id": "789",   // Not found in lookup JSON
    "field1": "val5",
    "field2": "val6"
  }
]

输入文件#2: input2.json

[
  {
    "joinId": "123",
    "joinField1": "AAAAAA",
    "joinField2": "BBBBBB"
  },
  {
    "joinId": "456",
    "joinField1": "CCCCCC",
    "joinField2": "DDDDDD"
  }
]

期望的输出:

[
  {
    "id": "item1",
    "join_id": "123",
    "field1": "val1",
    "field2": "val2",
    "joinId": "123",
    "joinField1": "AAAAAA",
    "joinField2": "BBBBBB"
  },
  {
    "id": "item2",
    "join_id": "123",
    "field1": "val1",
    "field2": "val2",
    "joinId": "123",
    "joinField1": "AAAAAA",
    "joinField2": "BBBBBB"
  },
  {
    "id": "item3",
    "join_id": "456",
    "field1": "val3",
    "field2": "val4",
    "joinId": "456",
    "joinField1": "CCCCCC",
    "joinField2": "DDDDDD"
  },
  {
    "id": "item4",
    "join_id": "789",
    "field1": "val5",
    "field2": "val6"
  }
]

如果joinId, joinField1,joinField2字段被添加到“item4”记录中也是可以的,只要它们具有null或为空的字符串值。

对此的任何帮助将不胜感激。

pmf

使用 jq 1.6,您只需使用INDEXand JOIN

jq '[JOIN(INDEX(input[]; .joinId); .[]; .join_id; add)]' input1.json input2.json

演示

对于 jq 1.5 解决方案,您可以从Github 存储库中获取这两个内置函数的定义并在您的代码中重新实现它们:

jq '
  def INDEX(stream; idx_expr):
    reduce stream as $row ({}; .[$row|idx_expr|tostring] = $row);
  def JOIN($idx; stream; idx_expr; join_expr):
    stream | [., $idx[idx_expr]] | join_expr;
  [JOIN(INDEX(input[]; .joinId); .[]; .join_id; add)]
' input1.json input2.json

演示

但是,您也可以将这两个定义相乘以直接适合您的用例:

jq '
  (reduce input[] as $row ({}; .[$row.joinId] = $row)) as $idx
  | map(. + $idx[.join_id])
' input1.json input2.json

演示

输出:

[
  {
    "id": "item1",
    "join_id": "123",
    "field1": "val1",
    "field2": "val2",
    "joinId": "123",
    "joinField1": "AAAAAA",
    "joinField2": "BBBBBB"
  },
  {
    "id": "item2",
    "join_id": "123",
    "field1": "val1",
    "field2": "val2",
    "joinId": "123",
    "joinField1": "AAAAAA",
    "joinField2": "BBBBBB"
  },
  {
    "id": "item3",
    "join_id": "456",
    "field1": "val3",
    "field2": "val4",
    "joinId": "456",
    "joinField1": "CCCCCC",
    "joinField2": "DDDDDD"
  },
  {
    "id": "item4",
    "join_id": "789",
    "field1": "val5",
    "field2": "val6"
  }
]

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章