Having trouble writing a group by query

[haqkap]

Here's the issue, my data is in JSON Format (see example below).

Each event that I have is associated with a user_id and has an array of assignments. Each assignment has an associated content_object_id, that is not unique, and scoring data.

I need to do figure out averages based on the student's scoring data. So my first query works well:

source="xyz.json" | stats avg(assignments{}.scoring_data.scoredPoints) by user_id

What I'm having trouble with is taking one step down and grouping the scoring data based on content_object_id for each user_id. These content_object_ids represent a specific sub-lesson.
So at the end what I'd like to see is a break down of a students average score, grouped by content_object_id.

I've tried this:

source="xyz.json" | stats avg(assignments{}.scoring_data.scoredPoints) as Average by user_id | FIELDS username, Average

But didn't work.

Here's the JSON:

Event 1:

 user_id: 1234
 assignments : [
{[-]
  content_object_id : 1,
  scoring_data : {scoredPoints:1},    
  ]

  content_object_id : 1,
  scoring_data : {scoredPoints:1},    
  ]

  content_object_id : 2,
  scoring_data : {scoredPoints:1},    
  ]

  content_object_id : 2,
  scoring_data : {scoredPoints:0},    
  ]}

Event 2:

user_id: 5678
assignments : [
{[-]
  content_object_id : 1,
  scoring_data : {scoredPoints:1},    
  ]

  content_object_id : 1,
  scoring_data : {scoredPoints:1},    
  ]

  content_object_id : 2,
  scoring_data : {scoredPoints:0},    
  ]

  content_object_id : 2,
  scoring_data : {scoredPoints:0},    
  ]}

[_d_]

Try using chart instead:

my_search_here | chart avg(measure) over user_id by object_id

d.