Sorting and transforming JSON with jq

In this topic, we will learn how to sort JSON objects by keys and values, iterate through and perform transformations, filter with boolean masks, and perform mathematical operations with the jq utility on the school.json file.

Sorting by keys and values

You can manipulate the array in several ways. If the array contains objects, you can sort it by its keys; otherwise, you sort by its values. You sort an array by its keys with the sort_by function and by its values with the sort function.

For example, the first and second objects in the array associated with the .students key is:

[
  {
    "firstName": "Emily",
    "lastName": "Anderson",
    "age": 20,
    "major": "Computer Science",
    "grades": {
      "math": 85,
      "programming": 92,
      "data structures": 88
    }
  },
  {
    "firstName": "Daniel",
    "lastName": "Nguyen",
    "age": 22,
    "major": "Business Administration",
    "grades": {
      "business management": 80,
      "economics": 88,
      "marketing": 92
    }
  }
]

If you sort by the first name key with the command:

jq '.students | .[:2] | sort_by(.firstName)' school.json

The resulting array is:

[
  {
    "firstName": "Daniel",
    "lastName": "Nguyen",
    "age": 22,
    "major": "Business Administration",
    "grades": {
      "business management": 80,
      "economics": 88,
      "marketing": 92
    }
  },
  {
    "firstName": "Emily",
    "lastName": "Anderson",
    "age": 20,
    "major": "Computer Science",
    "grades": {
      "math": 85,
      "programming": 92,
      "data structures": 88
    }
  }
]

You can sort the values associated with the .firstName key with the sort command:

jq '.students | [.[] | .firstName] | sort' school.json

which gives the output:

[
  "Ava",
  "Bob",
  "Daniel",
  "Emily",
  "Ethan",
  "Jada"
]

Here, we pipe the array of objects associated with the .students key into the .[] filter. The output of this filter, which is the individual object, is passed through a pipe and the values associated with their .firstName are taken. Since both operations take place inside an array [.[] | .firstName] , the values of .firstName are stored in this array. The array is then sorted with the sort function.

The sort and sort_by functions perform sorting operations in ascending order. You can perform sorting in descending order by piping the output of the sorting function to the reverse function:

jq '.students | .[:2] | sort_by(.firstName) | reverse' school.json

Iterating through array and object

The map function allows you to iterate over and perform operations on individual elements of the input array. For example, you can find the length of each student's first name in the array with the map and length functions.

jq '.students | [.[] | .firstName] | sort | map(length)' school.json

The command outputs:

[
  3,
  3,
  6,
  5,
  5,
  4
]

The map_values works like the map function when an array of values is passed through it. For example, we can find the length of each student's first name using map_values with the command:

jq '.students | map_values(.firstName | length) | sort' school.json

There is, however, one difference between the map and map_values functions. Unlike the map function, map_values can take an object input, modify the values associated with the keys, and output the modified object.

You can use the map_values function to deduct 10 points from the grades of the first and second students with the command:

jq '.students[:2] | .[] | .grades | map_values(. - 10)' school.json

which gives the output:

{
  "math": 75,
  "programming": 82,
  "data structures": 78
}
{
  "business management": 70,
  "economics": 78,
  "marketing": 82
}

Filtering with a boolean mask

The select function filters a JSON object with a boolean mask. The boolean expression is evaluated for each element. Only elements that evaluate to true are included in the result.

Let's use this function to return only students enrolled in the Computer Science major:

jq '.students | .[] | select(.major | startswith(("Comp", "comp")))' school.json

which gives the output:

{
  "firstName": "Emily",
  "lastName": "Anderson",
  "age": 20,
  "major": "Computer Science",
  "grades": {
    "math": 85,
    "programming": 92,
    "data structures": 88
  }
}
{
  "firstName": "Jada",
  "lastName": "Jordan",
  "age": 19,
  "major": "Computer Science",
  "grades": {
    "math": 78,
    "programming": 85,
    "data structures": 92
  }
}

Only two students are enrolled in the Computer Science major. We have used ("Comp", "comp") inside startswith so that the function captures instances where computer science is spelled with a lower or sentence case. The startswith function returns a boolean expression into the select function, which then returns the JSON objects where the boolean values are true.

The boolean mask can be generated by relational (>, <, ==, !=) or logical (and, or, not) operators or any function that return boolean expressions like startswidth and endswith.

Let's use relational and logical operators to filter the JSON object to find entries where the first name is Jada or the last name is Nguyen:

jq '.students | .[] |  select((.firstName == "Jada") or (.lastName == "Nguyen"))' school.json

The command returns:

{
  "firstName": "Daniel",
  "lastName": "Nguyen",
  "age": 22,
  "major": "Business Administration",
  "grades": {
    "business management": 80,
    "economics": 88,
    "marketing": 92
  }
}
{
  "firstName": "Jada",
  "lastName": "Jordan",
  "age": 19,
  "major": "Computer Science",
  "grades": {
    "math": 78,
    "programming": 85,
    "data structures": 92
  }
}

Mathematical operations with jq

You can perform a lot of mathematical operations with jq. Mathematical operations are conveniently performed inside the parenthesis or (). Let us start with the basic addition and subtraction operations.

Let's add 10 points to Jada's math score and deduct 10 points from Jada's data structures score:

jq '.students | .[] | select(.firstName == "Jada") | {
  firstName: .firstName,
  math: (.grades.math + 10),
  "data structures": (.grades | .["data structures"] - 10)
}' school.json

The command outputs:

{
  "firstName": "Jada",
  "math": 88,
  "data structures": 82
}

You can also perform addition with the add function. Let's use the add function to sum the grades for the first and second students:

jq '
  [
    .students[:2] | .[] | {
      firstName: .firstName,
      total: (.grades | add)
    }
  ]
' school.json

The output is:

[
  {
    "firstName": "Emily",
    "total": 265
  },
  {
    "firstName": "Daniel",
    "total": 260
  }
]

Next, let's find the average score for the first and second students:

jq '
  [
    .students[:2] | .[] | {
      firstName: .firstName,
      average: (.grades | (add / length) | round)
    }
  ]
' school.json

The output is:

[
  {
    "firstName": "Emily",
    "average": 88
  },
  {
    "firstName": "Daniel",
    "average": 87
  }
]

To find the average, you divide the sum by the length (add / length) and round to the nearest whole number.

Next, let's find the square root and squared age for the first and second students:

jq '
  [
    .students[:2] | .[] | {
      firstName: .firstName,
      age_sq: (.age | . * .),
      age_sqrt: (.age | sqrt)
    }
  ]
' school.json

The output is:

[
  {
    "firstName": "Emily",
    "age_sq": 400,
    "age_sqrt": 4.47213595499958
  },
  {
    "firstName": "Daniel",
    "age_sq": 484,
    "age_sqrt": 4.69041575982343
  }
]

Lastly, let us find the modulo of the highest over the lowest score for the first and second students:

jq '
  [
    .students[:2] | .[] | {
      firstName: .firstName,
      mod: ((.grades | [.[]] | max) % (.grades | [.[]] | min))
    }
  ]
' school.json

which outputs:

[
  {
    "firstName": "Emily",
    "mod": 7
  },
  {
    "firstName": "Daniel",
    "mod": 12
  }
]

Conclusion

We have learned how to use the jq utility to sort, filter, and transform JSON data. We have seen how to:

• Sort JSON data by keys and values
• Iterate through arrays and objects
• Filter with a boolean mask
• Perform mathematical operations

These skills can be used to manipulate JSON data in a variety of ways such as sorting data, filtering data, and calculating statistics. We can also use them to create new JSON data from existing ones.

jq is an effective tool for working with JSON data. It is easy to learn and use, and it offers a wide range of capabilities. If you are working with JSON data, jq is a very valuable tool to know how to use. Explore the documentation of the jq utility to discover even more features.