jq · lesson 25 of 32 · Tables & lookups

INDEX & JOIN

INDEX(f) turns an array into an object keyed by what f gives for each element: a lookup table. INDEX(source; f) does the same for any stream. JOIN matches each element of one table with its entry in another.

Example 1A lookup table by code

Document

{
  "customers": [
    { "code": "ADA", "name": "Ada", "city": "London" },
    { "code": "LIN", "name": "Linus", "city": "Helsinki" },
    { "code": "GRA", "name": "Grace", "city": "New York" }
  ],
  "orders": [
    { "no": 101, "customer": "ADA", "item": "keyboard", "qty": 2 },
    { "no": 102, "customer": "GRA", "item": "monitor", "qty": 1 },
    { "no": 103, "customer": "ADA", "item": "mouse", "qty": 3 },
    { "no": 104, "customer": "LIN", "item": "cable", "qty": 10 }
  ]
}

Query

.customers | INDEX(.code) | map_values(.city)

.customers The array of customers.
INDEX(.code) Store each customer under the key .code gives, as one object.
map_values(.city) Keep just the city of each entry.

Result

{"ADA":"London","LIN":"Helsinki","GRA":"New York"}

Try it in the playground

Example 2Look up the customer of every order

Document

{
  "customers": [
    { "code": "ADA", "name": "Ada", "city": "London" },
    { "code": "LIN", "name": "Linus", "city": "Helsinki" },
    { "code": "GRA", "name": "Grace", "city": "New York" }
  ],
  "orders": [
    { "no": 101, "customer": "ADA", "item": "keyboard", "qty": 2 },
    { "no": 102, "customer": "GRA", "item": "monitor", "qty": 1 },
    { "no": 103, "customer": "ADA", "item": "mouse", "qty": 3 },
    { "no": 104, "customer": "LIN", "item": "cable", "qty": 10 }
  ]
}

Query

INDEX(.customers[]; .code) as $by_code | .orders[] | {order: .no, who: $by_code[.customer].name}

INDEX(.customers[]; .code) The table, built from a stream: every customer, keyed by code.
as $by_code Saved, to use in the steps that follow.
.orders[] Each order in turn.
$by_code[.customer].name The order's customer code is the key into the table; then read the name.

Result

{"order":101,"who":"Ada"}
{"order":102,"who":"Grace"}
{"order":103,"who":"Ada"}
{"order":104,"who":"Linus"}

Try it in the playground

Example 3JOIN does the matching

Document

{
  "customers": [
    { "code": "ADA", "name": "Ada", "city": "London" },
    { "code": "LIN", "name": "Linus", "city": "Helsinki" },
    { "code": "GRA", "name": "Grace", "city": "New York" }
  ],
  "orders": [
    { "no": 101, "customer": "ADA", "item": "keyboard", "qty": 2 },
    { "no": 102, "customer": "GRA", "item": "monitor", "qty": 1 },
    { "no": 103, "customer": "ADA", "item": "mouse", "qty": 3 },
    { "no": 104, "customer": "LIN", "item": "cable", "qty": 10 }
  ]
}

Query

INDEX(.customers[]; .code) as $by_code | JOIN($by_code; .orders[]; .customer; {order: .[0].no, city: .[1].city})

INDEX(.customers[]; .code) as $by_code The same table.
JOIN($by_code; .orders[]; .customer; For each order, find the table entry under .customer, and make the pair [order, customer].
{order: .[0].no, city: .[1].city} Shape each pair: .[0] is the order, .[1] its customer.

Result

{"order":101,"city":"London"}
{"order":102,"city":"New York"}
{"order":103,"city":"London"}
{"order":104,"city":"Helsinki"}

Try it in the playground

Good to know

When two elements give the same key the later one wins. Keys are always strings, so a numeric id gives the keys "1", "2"… and the lookup is $table[.id | tostring].
A key with no entry in the table gives null: JOIN pairs the element with null.
JOIN($table; f) over an array gives every [element, match] pair as one array; with a stream and a fourth argument, as above, each pair is shaped by it.

INDEX & JOIN

Examples

Example 1A lookup table by code

Document

Query

Result

Example 2Look up the customer of every order

Document

Query

Result

Example 3JOIN does the matching

Document

Query

Result

Good to know