Advanced jq Recipes: 15 Real-World JSON Transformations

The jq Struggle is Real

As developers, we've all been there - stuck with a JSON payload that's just not cooperating. We need to transform it, reshape it, and make it usable, but the struggle is real. That's where jq, the lightweight and flexible command-line JSON processor, comes in.

Table of Contents

• Grouping and Aggregating JSON Data
• Pivoting and Reshaping JSON Data
• Joining and Merging JSON Data
• Recursive Descent and Complex Data Structures
• Using Environment Variables and API Responses
• Outputting to CSV and Other Formats

Grouping and Aggregating JSON Data

When working with large datasets, it's often necessary to group and aggregate data to extract meaningful insights. jq provides the group_by function for this purpose. Let's consider an example where we have a JSON array of objects, each representing a user with their age and location:

[
  {"name": "John", "age": 25, "location": "New York"},
  {"name": "Alice", "age": 30, "location": "New York"},
  {"name": "Bob", "age": 25, "location": "Los Angeles"}
]

We can use group_by to group users by location and calculate the average age:

jq '.[] | group_by(.location)[] | {location, average_age: (map(.age) | add / length)}'

This will output:

[
  {"location": "New York", "average_age": 27.5},
  {"location": "Los Angeles", "average_age": 25}
]

Pivoting and Reshaping JSON Data

Sometimes, we need to pivot or reshape our JSON data to better suit our needs. jq provides the map and reduce functions for this purpose. Let's consider an example where we have a JSON array of objects, each representing a product with its category and price:

[
  {"category": "Electronics", "price": 100},
  {"category": "Electronics", "price": 200},
  {"category": "Fashion", "price": 50}
]

We can use map and reduce to create a pivot table with the category as the key and the sum of prices as the value:

jq '. | map({category, price}) | reduce .[] as $item ({}; . + {($item.category): ($item.price + (.[$item.category] // 0))})'

This will output:

{
  "Electronics": 300,
  "Fashion": 50
}

Joining and Merging JSON Data

When working with multiple datasets, it's often necessary to join or merge them to create a unified view. jq provides the join function for this purpose. Let's consider an example where we have two JSON arrays, one representing users and the other representing orders:

// users.json
[
  {"id": 1, "name": "John"},
  {"id": 2, "name": "Alice"}
]

// orders.json
[
  {"id": 1, "user_id": 1, "total": 100},
  {"id": 2, "user_id": 1, "total": 200},
  {"id": 3, "user_id": 2, "total": 50}
]

We can use join to merge the two datasets on the user_id field:

jq -s '.[0] as $users | .[1] as $orders | $orders[] | . + {user: ($users[] | select(.id == .user_id))}'

This will output:

[
  {"id": 1, "user_id": 1, "total": 100, "user": {"id": 1, "name": "John"}},
  {"id": 2, "user_id": 1, "total": 200, "user": {"id": 1, "name": "John"}},
  {"id": 3, "user_id": 2, "total": 50, "user": {"id": 2, "name": "Alice"}}
]

Recursive Descent and Complex Data Structures

When working with complex data structures, jq provides the recurse function to recursively traverse and process the data. Let's consider an example where we have a JSON object with nested arrays and objects:

{
  "name": "John",
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY"
  },
  "orders": [
    {"id": 1, "total": 100},
    {"id": 2, "total": 200}
  ]
}

We can use recurse to extract all the values from the nested data structure:

jq '.. | .[]? | select(type == "string" or type == "number")'

This will output:

[
  "John",
  "123 Main St",
  "New York",
  "NY",
  100,
  200
]

Using Environment Variables and API Responses

jq can also be used to process API responses and environment variables. Let's consider an example where we have an API response with a JSON payload:

{
  "data": [
    {"id": 1, "name": "John"},
    {"id": 2, "name": "Alice"}
  ]
}

We can use jq to extract the data field and store it in an environment variable:

export DATA=$(jq '.data' response.json)

Outputting to CSV and Other Formats

Finally, jq provides the @csv function to output data in CSV format. Let's consider an example where we have a JSON array of objects:

[
  {"name": "John", "age": 25},
  {"name": "Alice", "age": 30}
]

We can use @csv to output the data in CSV format:

jq '.[] | @csv'

This will output:

"John",25
"Alice",30

Key Takeaways

• Use group_by to group and aggregate JSON data
• Use map and reduce to pivot and reshape JSON data
• Use join to merge multiple JSON datasets
• Use recurse to recursively traverse and process complex data structures
• Use environment variables and API responses to process dynamic data

FAQ

Q: What is jq and why should I use it?

A: jq is a lightweight and flexible command-line JSON processor that allows you to transform, reshape, and extract data from JSON payloads.

Q: How do I install jq?

A: You can install jq using your package manager or by downloading the binary from the official website.

Q: Can I use jq with other programming languages?

A: Yes, jq can be used with any programming language that supports JSON data, including Python, JavaScript, and Ruby.