CODEWITHSUNDEEP
jq
wass rubleff
wass rubleff

Reputation: 376

JQ: How to join arrays by key?

How can I expand array with a category stored in a dictionary?

E.g. for the following input,

{"weigths":
  [
    {"name":"apple","weigth":200},
    {"name":"tomato", "weigth":100}
  ],
 "categories":
 [
   {"name":"apple","category":"fruit"},
   {"name":"tomato","category":"vegetable"}
 ]
}

I need a performance-efficient way to append respective category to each object in weights, resulting in output:

{"weigths":
  [
    {"name":"apple","weigth":200, "category": "fruit"},
    {"name":"tomato", "weigth":100, "category": "vegetable"}
  ],
}

Is it something for JOIN/4? (I never tried it)

Update:

Ideally I would like to deal with a not-that-SQL object for categories: full input looking like this

{"weigths":
  [
    {"name":"apple","weigth":200},
    {"name":"orange", "weigth":300}
    {"name":"tomato","weigth":100},
    {"name":"spinach","weigth":50},
  ],
 "categories":
 {
   "fruit": ["apple", "orange"],
   "vegetable": ["tomato", "spinach"]
 }
}

...still getting a similar output:

{"weigths":
  [
    {"name": "apple", "weigth": 200, "category": "fruit"},
    {"name": "orange", "weigth": 300, "category": "fruit"},
    {"name": "tomato", "weigth": 100, "category": "vegetable"}
    {"name": "spinach", "weigth": 50, "category": "vegetable"}
  ],
}

Upvotes: 1

Views: 375

Answers (2)

pmf
pmf

Reputation: 36251

Using the SQL-Style Operators JOIN and INDEX would be:

{weights: [JOIN(INDEX(.categories[]; .name); .weigths[]; .name; add)]}

{
  "weights": [
    {
      "name": "apple",
      "weigth": 200,
      "category": "fruit"
    },
    {
      "name": "tomato",
      "weigth": 100,
      "category": "vegetable"
    }
  ]
}

Demo

For the updated structure, the INDEX of .categories needs to be tweaked (e.g. employing to_entries) in order to provide the .key as .category for each array item:

{weights: [JOIN(
  # INDEX(.categories[]; .name);    # This has been replaced with:
    INDEX(.categories | to_entries[]; .value[]) | .[] |= {category: .key};
.weigths[]; .name; add)]}

{
  "weights": [
    {
      "name": "apple",
      "weigth": 200,
      "category": "fruit"
    },
    {
      "name": "orange",
      "weigth": 300,
      "category": "fruit"
    },
    {
      "name": "tomato",
      "weigth": 100,
      "category": "vegetable"
    },
    {
      "name": "spinach",
      "weigth": 50,
      "category": "vegetable"
    }
  ]
}

Demo

Upvotes: 3

Sela M.
Sela M.

Reputation: 31

You may be interested in checking out ~Q (pronounced "unquery"), a query language that I very recently released, that just like jq can run as a command-line tool.

https://github.com/xcite-db/Unquery

In the first case, the query would be:

{
    "#var cat:categories[]": {
          "$(name)":"category"
    },
    "#return:weigths[]": [{
       "{}:" : ".",
       "category":"$var(cat).$(name)"
    }]
}

And in the updated example, the query is:

{
    "#var cat: categories:{}:[]": {
          "$(.)": "$key"
    },
    "#return:weigths[]": [{
       "{}:": ".",
       "category":"$var(cat).$(name)"
    }]
}

In either case, the trick is to start by generating a map from fruits to categories, and then use this map to add the category to each entry. So the difference is only in the first part (generating the map).

A variable in ~Q can contain any JSON value/document. The code:

    "#var cat: categories:{}:[]": {
          "$(.)": "$key"
    },

Converts categories in-memory into:

{
    "apple": "fruit",
    "orange": "fruit",
    "spinach": "vegetable",
    "tomato": "vegetable"
}

And after that, you can use it to retrieve the category with $var(cat).$(name). If you have a different structure for categories, all you need to do is to change that part of the code to generate the mapping from the categories structure.

Upvotes: 1

Related Questions