Need advice on unnesting totaling and grouping array elements from separate documents

SQL++
1

I’m trying to figure out a query to get all user status totals of each user that have logged into an area of our application within a certain time grouped by the application area.

We keep a record of each session that a user has and the duration of their “active” and “inactive”
states within that session.

So if my data looks like this:

{"userId":"user:1",
 "location":"beach",
"start":0,
"end":22,
    "statuses":[ 
         { "start":1, 
            "end":4,
            "duration":3,
            "status":"active"
          },    
         { "start":5, 
            "end":10,
            "duration":5,
            "status":"inactive"
          },
         { "start":11, 
            "end":16,
            "duration":5,
            "status":"active"
          },
          { "start":17, 
            "end":22,
            "duration":5,
            "status":"inactive"
          },
     ]
},
{"userId":"user:1",
 "location":"beach",
"start":30,
"end":45,
    "statuses":[ 
         { "start":30, 
            "end":39,
            "duration":9,
            "status":"active"
          },    
         { "start":40, 
            "end":45,
            "duration":5,
            "status":"inactive"
     ]
},
{"userId":"user:2",
 "location":"beach",
 "start":3,
 "end":10,
    "statuses":[ 
         { "start":1, 
            "end":4,
            "duration":3,
            "status":"active"
          },    
          { "start":5, 
            "end":10,
            "duration":5,
            "status":"inactive"
          }
     ]
},
{"userId":"user:2",
 "location":"mountains",
 "start":11,
 "end":20,
    "statuses":[ 
         { "start":11, 
            "end":16,
            "duration":5,
            "status":"active"
          },    
          { "start":17, 
            "end":20,
            "duration":3,
            "status":"inactive"
          }
     ]
}

I’d like to find out the status totals for each user in each area starting at 0 and ending at 39
and get results like:

{
	"results": [{
			"area": "beach",
			"users": [{
					"userId": "user:1",
					"active_total": 17,
					"inactive_total": 10
				},
				{
					"userId": "user: 2",
					"active_total": 3,
					"inactive_total": 5
				}
			]
		},
		{
			"area": "mountains",
			"users": [{
				"userId": "user: 2",
				"active_total": 5,
				"inactive_total": 3
			}]
		}
	]
}

How should i go about doing this?
I’ve been trying to UNNEST all session.statuses between 0 and 39
and GROUPING them by area but then I get a little stuck totaling the durations per user.
Are there any N1QL experts that could help me out?

2 
SELECT t.area, ARRAY_AGG({t.usedId,t.active_total, t.inactive_total}) AS users
FROM ( SELECT d.location AS area, d.userId,
              SUM(CASE WHEN su.status = "active" TEHN 1 ELSE 0 END) AS active_total,
              SUM(CASE WHEN su.status = "inactive" TEHN 1 ELSE 0 END) AS inactive_total
       FROM default AS d
       UNNEST d.statuses AS su
       WHERE d.start >= 0 AND d.end <= 39
       GROUP BY d.location, d.userId) AS t
GROUP BY t.area;

OR without UNNEST

SELECT t.area, ARRAY_AGG({t.usedId,t.active_total, t.inactive_total}) AS users
FROM ( SELECT d.location AS area, d.userId,
           SUM(ARRAY_SUM(ARRAY 1 FOR su IN d.statuses WHEN  su.status = "active" END)) AS active_total,
           SUM(ARRAY_SUM(ARRAY 1 FOR su IN d.statuses WHEN su.status = "inactive" END)) AS inactive_total
       FROM default AS d
       WHERE d.start >= 0 AND d.end <= 39
      GROUP BY d.location, d.userId) AS t
GROUP BY t.area;
1 Like
3

@vsr1
Oh a subquery! I hadn’t thought of that. That looks like it would do the trick. I’ll give it a try. Thank you for the fast response!

4

You need multi (two) level aggregates. As nested aggregates are not allowed. You need to use each level aggregation in subquery.

5

That’s exactly what i was running into.
When i tried to aggregate the UNNESTed statuses within another aggregation of the areas. I will try to apply these to my much more convoluted data!