Get COUNT() from subquery

SQL++
1

Hi
I have a N1QL query , in it I need add subquery with COUNT

SELECT  col_doc.*,
       game_count
FROM `cms-deploy` col_doc
game_count=(
select count(*) from `cms-deploy` AS col_g where  _type="game"  AND col_g.data.en.provider.id  = (
    SELECT raw col_d.id 
    FROM `cms-deploy` AS col_d USE KEYS META(col_doc).id where _type="providers_game" 
   )[0]
        )
    WHERE  col_doc._type="providers_game"

(This is a short query and in full query a can not use group by)

But game_count does not contain correct COUNT information only first “providers_game”

Could help me ?

2

Currently N1QL doesn’t support correlated subquery without USE KEYS.

WITH  gameobj AS  (OBJECT v.id:v.cnt
                   FOR v IN (SELECT TO_STR(g.data.en.provider.id) AS id , COUNT(1) AS cnt
                             FROM `cms-deploy` AS g 
                             WHERE g._type = "game" AND g.data.en.provider.id IS NOT NULL
                             GROUP BY g.data.en.provider.id)
                   END)
SELECT c.*, gameobj.[TO_STR(c.id)] AS game_count
FROM `cms-deploy`  AS c
WHERE c._type = "providers_game";
3

Thank you for reply!