Analogue for sql join in jq

Question

I have the following json:

[
  {"id": "1", "type": "folder", "title": "folder-1"},
  {"id": "2", "type": "folder", "title": "folder-2"},
  {"id": "3", "type": "item", "title": "item-1", "folder": "1"},
  {"id": "4", "type": "item", "title": "item-2", "folder": "2"},
  {"id": "5", "type": "item", "title": "item-3"}
]

Basically, I need to produce this output using jq, which is similar to the result of sql join:

[
  {"type": "item", "title": "item-1", "folder": "folder-1"},
  {"type": "item", "title": "item-2", "folder": "folder-2"},
  {"type": "item", "title": "item-3"}
]

Any ideas?

Answer 1

Try this filter:

map(
    (select(.type=="item") | { key: .folder, value: { type, title } }),
    (select(.type=="folder") | { key: .id, value: { folder: .title } })
)
| group_by(.key)
| map(
    (map(select(.key != null) | .value) | add)
    // map(.value)[]
)

You'll have to break this out into steps.

1. Get the items and folders and for each, take the values you're interested in and assign it a key to associate with.

   map(
       (select(.type=="item") | { key: .folder, value: { type, title } }),
       (select(.type=="folder") | { key: .id, value: { folder: .title } })
   )
   

2. Group all by the key

   | group_by(.key)
   

3. Then combine the values that have keys (folders) and the value otherwise

   | map(
       (map(select(.key != null) | .value) | add)
       // map(.value)[]
   )
   

Answer 2

jq 'map(select(has("folder") or (.["title"] | startswith("item"))) | del(.id))' sample_file

Output:

[
  {
    "type": "item",
    "title": "item-1",
    "folder": "1"
  },
  {
    "type": "item",
    "title": "item-2",
    "folder": "2"
  },
  {
    "type": "item",
    "title": "item-3"
  }
]

Answer 3

Here is another solution which works by separating the data into two objects $folders and $items and then constructing the desired result.

  (
    reduce map(select(.type == "folder"))[] as $f (
      {}
    ; .[$f.id] = $f
    )
  ) as $folders

| (
    reduce map(select(.type == "item"))[] as $i (
      {}
    ; .[$i.id] = $i
    ) 
  ) as $items

| [
      $items[]
    | {type, title, folder}
    | if .folder == null then del(.folder) else .folder = $folders[.folder].title end
  ]

If your version of jq has INDEX/2

def INDEX(stream; idx_expr):
  reduce stream as $row ({};
    .[$row|idx_expr|
      if type != "string" then tojson
      else .
      end] |= $row);

this can be simplified to

  INDEX(.[] | select(.type == "folder"); .id) as $folders
| INDEX(.[] | select(.type == "item"); .id) as $items
| [
      $items[]
    | {type, title, folder}
    | if .folder == null then del(.folder) else .folder = $folders[.folder].title end
  ]