[SOLVED] jq - unable to correctly perform multiple selects

Issue

I've been reading through other posts and jq reference pages but I cannot seem to get this working.

Example data

{
    "member": [
    {
        "firstName": "Paul"
        "lastName": "Simons"
        "certificates": [
            {
                "status" : "Pass",
                "grade" : {
                  "type" : "music",
                  "level" : 3
                },
            "awardDate" : "2023-01-28"
            }
            {
                "status" : "Pass",
                "grade" : {
                  "type" : "sports",
                  "level" : 2
                },
            "awardDate" : "2021-02-02"
            }
                        {
                "status" : "Pass",
                "grade" : {
                  "type" : "music",
                  "level" : 2
                },
            "awardDate" : "2021-02-02"
            }
    }
]
}

The aim is to list: each members name, date of most senior grade (per type), type of grade, level of grade.

Paul Simmons, [music, 3, 2023-01-28], [sports, 2, 2021-02-02].

Where I've gotten to before my head exploded:

jq -r '.member[] |
[.firstName + " " + .lastName, (.certificates
| sort_by(.awardDate)
| (.[-1].awardDate) as $max
| .[]
| select(.grade.type)
| select(.awardDate == $max)
| [(.awardDate | fromdateiso8601 | strflocaltime("%Y-%m-%d")), .grade.type, .grade.level])'] members.json

Determining the most recent level (per type of certificate), could also be finding the highest level, rather than using date - however I was having trouble.

Solution

Given a JSON input like

{
  "member": [
    {
      "firstName": "Paul",
      "lastName": "Simons",
      "certificates": [
        {
          "status": "Pass",
          "grade": {
            "type": "music",
            "level": 3
          },
          "awardDate": "2023-01-28"
        },
        {
          "status": "Pass",
          "grade": {
            "type": "sports",
            "level": 2
          },
          "awardDate": "2021-02-02"
        },
        {
          "status": "Pass",
          "grade": {
            "type": "music",
            "level": 2
          },
          "awardDate": "2021-02-02"
        }
      ]
    }
  ]
}

You could use group_by to combine same grade types, and max_by to get the one with the highest grade level:

.member[] | ["\(.firstName) \(.lastName)", (.certificates
  | group_by(.grade.type)[] | max_by(.grade.level)
  | "[\(.grade.type), \(.grade.level), \(.awardDate)]"
)] | join(", ") + "."

Paul Simons, [music, 3, 2023-01-28], [sports, 2, 2021-02-02].

Demo

Answered By - pmf

Answer Checked By - David Goodson (WPSolving Volunteer)