Query based on a field name (subdocuments)

SQL++
,
1

I have this data structure

{
“months”: {
“09-2021”: {
“logins”: {
“user1”: {
“total”: 5
},
“user2”: {
“total”: 2
},
“user3”: {
“total”: 3
},
“user4”: {
“total”: 1
}
}
},
“10-2021”: {
“logins”: {
“user1”: {
“total”: 1
},
“user2”: {
“total”: 2
}
}
}
}
}

and I want to count distinct user during a month.
My query looks something like

select count(logins) as compte from `bucket-name’ stats
UNNEST OBJECT_VALUES(stats.months) AS months
UNNEST OBJECT_NAMES(months) AS month
UNNEST OBJECT_VALUES(months.logins) AS logins
where meta(stats).id = ‘stats’ and month = ‘09-2021’;

But it doesn’t work ! I still get logins of ‘10-2021’

2

Since the filter is on a field name not on a data value, couldn’t you just:

select array_length(object_names(stats.months.`09-2021`.logins))  as compte
from `bucket-name` stats where meta().id = 'stats';

?

(The distinct requirement is implicitly taken care of in your document design - you can’t have duplicated field names.)

HTH.

3

You can use OBJECT_LENGTH ()vs array_length(object_names())

SELECT OBJECT_LENGTH(b.months.`09-2021`.logins) AS cnt
FROM `bucket-name` AS b  USE KEYS "stats";

For All months

SELECT  RAW OBJECT n:OBJECT_LENGTH(v.logins) FOR n:v IN b.months END
FROM `bucket-name` AS b  USE KEYS "stats";