了解jqJOIN()

我試圖理解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.

如果沒有例子，我覺得這很難理解。

你能舉一些例子來說明它是如何作業的嗎？

uj5u.com熱心網友回復：

這個函式應該類似于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

由于 jqJOIN一次只處理兩個表（更多，您需要級聯），讓我們忽略缺少的 Products 表。

在我們開始之前，JOIN我們需要先看一下INDEX，它將像我們上面的表一樣的陣列轉換為一個以表的“主鍵”作為欄位名稱的物件。這是合理的，因為欄位名稱是唯一的，使得查找總是回傳不超過一條記錄。

INDEX($customers[]; .CustomerID)

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

演示

Now, we can easily perform a JOIN operation between Orders (as the "left table") and their Customers (as the "right table"). Providing the "right table" as an INDEXed object, the "left table" as a stream .[], and the "related column" as field in the left table's objects that is matched with the right table's primary key (field name in the lookup object), we get: (let the last parameter be just . for now)

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

As you can see, we get a stream of arrays, one for each order. Each array has two elements: the record from the left table and the one from the right. An unsuccessful lookup would yield null on the right side.

Finally, the fourth parameter being the "join expression" describes how to join the two matching records, which esentially acts as a 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

標籤：加入 jq

上一篇：MySQLGROUPBY列和COUNT有條件地在同一個表中的另一列

下一篇：合并兩個不同的csv檔案并將它們合二為一