理解 jq JOIN()

我试图理解JOIN()内置于jq.

来自 jq 手册（https://stedolan.github.io/jq/manual):

JOIN($idx; stream; idx_expr; join_expr):

This builtin joins the values from the given stream to the given index. 
The index's keys are computed by applying the given index expression to each value from the given stream. 
An array of the value in the stream and the corresponding value from the index is fed to the given join expression to produce each result.

我发现如果没有例子这很难理解。

您能否举一些其用法示例来展示其工作原理？

这个函数应该类似于JOIN中的条款SQL。它用于组合来自两个（或多个）的行SQL) 表，基于它们之间的相关列。

让我们构建一些“表”。

第一个应该是带有 ID 的订单列表，ID 引用订购的客户和订购的产品：

[
  {
    "OrderID": "10",
    "CustomerIDRef": "2",
    "ProductIDRef": "7"
  },
  {
    "OrderID": "11",
    "CustomerIDRef": "1",
    "ProductIDRef": "7"
  },
  {
    "OrderID": "12",
    "CustomerIDRef": "2",
    "ProductIDRef": "14"
  },
  {
    "OrderID": "13",
    "CustomerIDRef": "2",
    "ProductIDRef": "7"
  }
]
as $orders

让第二个是映射到其姓名的客户列表：

[
  {
    "CustomerID": "1",
    "CustomerName": "Alfred"
  },
  {
    "CustomerID": "2",
    "CustomerName": "Bill"
  },
  {
    "CustomerID": "3",
    "CustomerName": "Caroline"
  }
]
as $customers

As jq's JOIN一次只能处理两个表（如果要处理更多，则需要级联），让我们忽略缺少的 Products 表。

在我们到达之前JOIN我们需要看看INDEX首先，它将像上面的表一样的数组转换为一个对象，其中表的“主键”作为字段名称。这是合理的，因为字段名称是唯一的，使得查找始终返回不超过一条记录。

INDEX($customers[]; .CustomerID)

{
  "1": {
    "CustomerID": "1",
    "CustomerName": "Alfred"
  },
  "2": {
    "CustomerID": "2",
    "CustomerName": "Bill"
  },
  "3": {
    "CustomerID": "3",
    "CustomerName": "Caroline"
  }
}

Demo

现在，我们可以轻松地执行JOIN订单（如“左表”）与其客户（如“右表”）之间的操作。提供“右表”作为INDEXed 对象，“左表”作为流.[]，以及左表对象中的“相关列”作为与右表主键（查找对象中的字段名称）匹配的字段，我们得到：（令最后一个参数为.目前）

JOIN(INDEX($customers[]; .CustomerID); $orders[]; .CustomerIDRef; .)

[
  {
    "OrderID": "10",
    "CustomerIDRef": "2",
    "ProductIDRef": "7"
  },
  {
    "CustomerID": "2",
    "CustomerName": "Bill"
  }
]
[
  {
    "OrderID": "11",
    "CustomerIDRef": "1",
    "ProductIDRef": "7"
  },
  {
    "CustomerID": "1",
    "CustomerName": "Alfred"
  }
]
[
  {
    "OrderID": "12",
    "CustomerIDRef": "2",
    "ProductIDRef": "14"
  },
  {
    "CustomerID": "2",
    "CustomerName": "Bill"
  }
]
[
  {
    "OrderID": "13",
    "CustomerIDRef": "2",
    "ProductIDRef": "7"
  },
  {
    "CustomerID": "2",
    "CustomerName": "Bill"
  }
]

Demo

正如您所看到的，我们得到一个数组流，每个订单一个。每个数组都有两个元素：左表中的记录和右表中的记录。不成功的查找会产生null在右侧。

最后，第四个参数是“连接表达式”，描述了如何连接两个匹配的记录，它本质上充当了一个map.

JOIN(INDEX($customers[]; .CustomerID); $orders[]; .CustomerIDRef;
  "\(.[0].OrderID): \(.[1].CustomerName) ordered Product #\(.[0].ProductIDRef)."
)

10: Bill ordered Product #7.
11: Alfred ordered Product #7.
12: Bill ordered Product #14.
13: Bill ordered Product #7.

Demo