Complex jq Filter

Question

Disclaimer: this is not homework, this is a sanitized version of something I'm doing for work.

I'm trying to filter some json data using jq and want to return an object for each matching record in my filter. Here's a text description of what I'm doing: "Given a JSON object containing a list of students, some personal info, and their grades, return a list of the name, ages, and cumulative gpa for each student that received an A in CSC101."

Here's the test JSON object (program input):

{
    "students": [
        {"name": "John", "age": "19", "gender": "m", "from": "Tampa, FL", "cum_gpa": "3.83", "semesters": [
            {"name": "201302", "gpa": "3.67", "grades": {"CSC101": "A", "MAT101": "A", "PSY101": "B"}},
            {"name": "201401", "gpa": "4.00", "grades": {"CSC201": "A", "MAT201": "A", "HIS101": "A"}}
        ]},
        {"name": "Mary", "age": "20", "gender": "f", "from": "Chicago, IL", "cum_gpa": "3.50", "semesters": [
            {"name": "201302", "gpa": "4.00", "grades": {"CSC101": "A", "MAT101": "A", "ECO101": "A"}},
            {"name": "201401", "gpa": "3.00", "grades": {"CSC201": "B", "MAT201": "B", "HUM101": "B"}}
        ]},
        {"name": "Dan", "age": "20", "gender": "m", "from": "Seattle, WA", "cum_gpa": "3.33", "semesters": [
            {"name": "201302", "gpa": "3.33", "grades": {"CHE101": "B", "MAT101": "A", "PSY101": "B"}},
            {"name": "201401", "gpa": "3.33", "grades": {"CHE201": "A", "MAT201": "A", "HUM101": "C"}}
        ]}
    ]
}

Here's my current filter expression and the result:

cat test.json |jq -c '.students[]|select(.semesters[].grades.CSC101 == "A")|{name: .name, age: .age, gpa: .cum_gpa, CSC101: .semesters[].grades.CSC101}' 
{"name":"John","age":"19","gpa":"3.83","CSC101":"A"}
{"name":"John","age":"19","gpa":"3.83","CSC101":null}
{"name":"Mary","age":"20","gpa":"3.50","CSC101":"A"}
{"name":"Mary","age":"20","gpa":"3.50","CSC101":null}

It's sort-of working, but it generates extra output. I only want it to yield the semesters that have an entry for CSC101, but instead it returns all semesters for any student that took CSC101. Any suggestions?

Answer 1

Try this filter:

.students | map(
    {
        name,
        age,
        gpa: .cum_gpa,
        CSC101: .semesters | map(.grades) | add | .CSC101
    }
    |
    select(.CSC101 == "A")
)

The idea is to merge all grades from all semesters for each student and grab the CSC101 grade. Then filter the students out that have an A grade.

This results in:

[
  {
    "name": "John",
    "age": "19",
    "gpa": "3.83",
    "CSC101": "A"
  },
  {
    "name": "Mary",
    "age": "20",
    "gpa": "3.50",
    "CSC101": "A"
  }
]