Is the possible to get a count based on items in Array?

SQL++
1

I have docs which have and array lets call it buckets ad the bucket array holds guid’s for buckets. Not every doc will have all buckets. So how would i get a count list of in how many docs a bucket is present.

Select Count(*), c.`buckets` from Contacts c
WHERE _type= "contact"
group by c.`buckets`

when i use the basic group by it gives me the count of how often i combo of different buckets are used not how often the bucket item is overall used in buckets array.

If i try to query a list of all buckets and use sub query it doesn’t work either

SELECT text,
       META().id AS id,
       (
           SELECT RAW COUNT(*) AS Count
           FROM Contacts c
           WHERE _type= "contact"
               AND ANY b IN c.buckets SATISFIES b IN [ d.id ] END) AS Count
FROM Contacts d
WHERE _type ='bucket'

it complains about key

“msg”: “Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM Contacts.”

2 
SELECT b, COUNT(1) AS cnt
FROM  Contacts AS c
UNNEST c.buckets AS b
WHERE c._type= "contact"
GROUP BY b;
3

Since this is a group query how would i get the text for the id from the bucket query ? when i use the blow i get
“msg”: “Expression (correlated (select raw (Contacts.text) from Contacts use keys b)[0]) must depend only on group keys or aggregates.”,

SELECT b,
       COUNT(1) AS cnt,
       (
           SELECT RAW text
           FROM Contacts USE KEYS b)[0] AS Text
FROM Contacts AS c
UNNEST c.buckets AS b
WHERE c._type= "contact"
GROUP BY b;

but if i using a static bucket key like below it works fine

SELECT b as `Bucket`,
       COUNT(1) AS cnt,
       (
           SELECT RAW text
           FROM Contacts USE KEYS 'bucket::076aa51a-5de0-4151-a0d2-7ae52cd19dd1')[0] AS Text
FROM Contacts AS c
UNNEST c.buckets AS b
WHERE c._type= "contact"
GROUP BY b;
4 
SELECT d.cnt, d.b, j1.txt 
FROM (SELECT b, COUNT(1) AS cnt
      FROM  Contacts AS c
      UNNEST c.buckets AS b
      WHERE c._type= "contact"
      GROUP BY b) AS d 
JOIN Contacts AS j1 ON KEYS d.b;
5

Is there any plans to support basic sub queries, i know i should be able for now if it uses a KEY ? I could have seen the issue being that there is a aggregate but that wasn’t the issue when i had a static KEY. When i used the dynamic key it didn’t like it anymore.

6

For aggregate/Group by the projections must only depend on group expressions or must be aggregates.
In this case there is correlated subquery that why it returns error it should have detected correlation is on group key. You can use intervene subquery. May be in future release it can be enhanced.

SELECT d.cnt, d.b,  text
FROM (SELECT b, COUNT(1) AS cnt
      FROM  Contacts AS c
      UNNEST c.buckets AS b
      WHERE c._type= "contact"
      GROUP BY b) AS d 
LET text = (SELECT RAW text FROM Contacts USE KEYS d.b)[0];
1 Like