JQ puzzling with Adam after the May 2023 RemoteHack

jq_pupil_aggregating.md

JQ puzzling with Adam after the May 2023 RemoteHack

Given the input:

INPUT_JSON='{
  "students": [
    {
      "name": "Jane",
      "grades": {
        "results": {
          "art": [
            24,
            54,
            43
          ],
          "history": [
            98,
            78,
            55
          ],
          "math": [
            55,
            55,
            55
          ]
        }
      }
    },
    {
      "name": "Tess",
      "grades": {
        "results": {
          "art": [
            98,
            97,
            95
          ],
          "history": [
            12,
            15,
            15
          ],
          "math": [
            55,
            56,
            54
          ]
        }
      }
    },
    {
      "name": "Cameron",
      "grades": {
        "results": {
          "art": [
            62,
            95,
            90
          ],
          "history": [
            67,
            89,
            50
          ],
          "math": [
            93,
            73,
            67
          ]
        }
      }
    }
  ]
}'

Retrieve the students scores and find the highest-scoring pupil for each subject

# Find the highest scorer for art:
echo $INPUT_JSON | jq '.students | max_by(.grades.results.art)'

# Retrieve the list of all subjects:
echo $INPUT_JSON | jq '.students | map(.grades.results) | map(keys) | flatten | unique'

# Store the students in a variable so we can reference it later
echo $INPUT_JSON | jq '.students as $students | map(.grades.results) | map(keys) | flatten | unique'

# Find the highest scorer for each subject
echo $INPUT_JSON | jq '.students as $students | $students | map(.grades.results) | map(keys) | flatten | unique | [ .[] as $subject | $subject | {subject: ., highest: [ $students | max_by(.grades.results[$subject])][0]}]'

# Find the highest scorer for each subject and print their total score for that subject
echo $INPUT_JSON | jq '.students as $students | $students | map(.grades.results) | map(keys) | flatten | unique | [ .[] as $subject | $subject | {subject: ., highest: [ $students | max_by(.grades.results[$subject]) | {name: .name, total_score: [.grades.results[$subject] | add][0]}][0]}]'

The output of the final command is:

[
  {
    "subject": "art",
    "highest": {
      "name": "Tess",
      "total_score": 290
    }
  },
  {
    "subject": "history",
    "highest": {
      "name": "Jane",
      "total_score": 231
    }
  },
  {
    "subject": "math",
    "highest": {
      "name": "Cameron",
      "total_score": 233
    }
  }
]