Reusing a subquery (in both where and select) inside a query

SQL++
,
1

Hello,
I hope this kind of question is allowed; also, thank you a lot in advance for reading. ^-^

I am trying to write a N1QL query that gets in the result the minimum of a count with another field . As far as I know, the only way to do so is to use the same subquery twice, in this way:

SELECT subquery.parameter, subquery.count
FROM /* <SUBQUERY>, with a field "count" that is a COUNT(*) of other stuff*/ AS subquery
WHERE subquery.count WITHIN (SELECT MIN(...) FROM /*<SUBQUERY>*/)

(I’m using a pseudo-query to simplify things, but I’ll paste my actual query at the end just to be safe.)

I can confirm that this works on my database, even if it’s very long and unreadable.

The subquery is quite long, so my question is: as it’s used identically twice, is there some way to “save” the subquery and use it in both the FROM and the SELECT clause? I come from an SQL background, so what I’m asking is if there is a way to create views (but I mean this in the SQL sense, not in the MapReduce/Couchbase sense).

If this is possible, I have a further question: is there a way to make it so that Couchbase will execute the subquery only once and then reuse it (aka, I’m saving the result itself) in both the FROM and SELECT?

I am working closely with the documentation, but all I have found is the LET construct - which, as far as understand, only deals with variables - and some people suggest using indexes but I don’t see how I would use them here; hence I’m asking.

Thank you a lot for your time. I’m attaching the query, but be wary it’s quite long; however, you can se most of it is the same subquery being repeated.

/*CREATE INDEX idx_poiname IF NOT EXISTS ON veronacard.veronacard_db.mini_POI_db(name); 
/*this is necessary for the outer join*/

SELECT daily_count.name, daily_count.countedswipes 
FROM ( /*SUBQUERY: find swipes count on 9/8/20 for every POI*/
        SELECT POInames.name,IFMISSINGORNULL(partial_count.countedswipes,0) AS countedswipes 
       /* I need this because otherwise if the POI has no swipes, it will not be included (like this, instead, it will be included with swipescount = 0) */
        FROM (
              SELECT POI1.name AS poiname, COUNT(*) AS countedswipes
              FROM veronacard.veronacard_db.mini_POI_db AS POI1 UNNEST POI1.swipes AS S1
                WHERE 
                DATE_PART_STR(S1.swipe_date, "year") = 2020 AND
                DATE_PART_STR(S1.swipe_date, "month") = 8 AND
                DATE_PART_STR(S1.swipe_date, "day") = 9
              GROUP BY POI1.name
              ) AS partial_count
        RIGHT JOIN (SELECT DISTINCT POI.name FROM veronacard.veronacard_db.mini_POI_db AS POI) 
                    AS POInames  ON  partial_count.poiname == POInames.name
    ) AS daily_count
WHERE daily_count.countedswipes WITHIN (
    SELECT MIN (daily_count1.countedswipes)
    FROM(
        SELECT POInames.name,IFMISSINGORNULL(partial_count.countedswipes,0) AS countedswipes
        FROM ( /*SUBQUERY AGAIN*/
              SELECT POI1.name AS poiname, COUNT(*) AS countedswipes
              FROM veronacard.veronacard_db.mini_POI_db AS POI1 UNNEST POI1.swipes AS S1
                WHERE 
                DATE_PART_STR(S1.swipe_date, "year") = 2020 AND
                DATE_PART_STR(S1.swipe_date, "month") = 8 AND
                DATE_PART_STR(S1.swipe_date, "day") = 9
              GROUP BY POI1.name
              ) AS partial_count
        RIGHT JOIN (SELECT DISTINCT POI.name FROM veronacard.veronacard_db.mini_POI_db AS POI) 
                    AS POInames  ON  partial_count.poiname == POInames.name
    ) AS daily_count1
)
2

You can assign the result of a sub-query in a LET statement, e.g.

SELECT t.a, b
FROM [{"a":1},{"a":2}] t
LET b = (SELECT RAW count(1) FROM  [1,2,3,4] t2)[0];

As long as your sub-query isn’t correlated, it will be evaluated only once.

You can achieve much the same with a WITH clause.

HTH.

3

Assume it is EE version and it can use index aggregation described here (You can verify doing EXPLAIN on subquery ) Index Grouping and Aggregation Overview - The Couchbase Blog

CREATE INDEX ix1 ON veronacard.veronacard_db.mini_POI_db(name);
CREATE INDEX ix2 ON veronacard.veronacard_db.mini_POI_db(ALL ARRAY DATE_FROMAT_STR(s.swipe_date,"1111-11-11") FOR s IN swipes END, name);
WITH swipeslist AS (SELECT p.name AS poiname, COUNT(1) AS countedswipes
                FROM veronacard.veronacard_db.mini_POI_db AS p
                UNNEST p.swipes AS s
                WHERE DATE_FROMAT_STR(s.swipe_date,"1111-11-11") = "2020-08-09"
                GROUP BY p.name),
      daily_count AS (SELECT n AS name, IFMISSINGORNULL(s.countedswipes, 0) AS countedswipes
                      FROM (SELECT RAW p.name
                            FROM veronacard.veronacard_db.mini_POI_db AS p
                            WHERE p.name IS NOT NULL
                            GROUP BY p.name) AS n
                      LEFT JOIN swipeslist AS s
                      ON s.poiname = n),
      min_count AS (ARRAY_MIN(daily_count[*].countedswipes))
SELECT d.*
FROM daily_count AS d
WHERE d.countedswipes = min_count;
4

Hi! Thank you a lot for your answer. I had seen the LET construct in documentation, but I was never able to use it in my query;I thought the reason was that the result from the query is only a “simple” value (one raw value), like in your example, as opposed to a more structured result which would be my case.

The way I tried to use it was

SELECT daily_count.name, daily_count.countedswipes 
FROM LET daily_count =(/*SUBQUERY*/
        SELECT POInames.name,IFMISSINGORNULL(partial_count.countedswipes,0) AS countedswipes 
        FROM (
              SELECT POI1.name AS poiname, COUNT(*) AS countedswipes
              FROM veronacard.veronacard_db.mini_POI_db AS POI1 UNNEST POI1.swipes AS S1
                WHERE 
                DATE_PART_STR(S1.swipe_date, "year") = 2020 AND
                DATE_PART_STR(S1.swipe_date, "month") = 8 AND
                DATE_PART_STR(S1.swipe_date, "day") = 9
              GROUP BY POI1.name
              ) AS partial_count
        RIGHT JOIN (SELECT DISTINCT POI.name FROM veronacard.veronacard_db.mini_POI_db AS POI) 
                    AS POInames  ON  partial_count.poiname == POInames.name)

WHERE daily_count.countedswipes WITHIN (
    SELECT MIN (daily_count1.countedswipes)
    FROM daily_count
)

But I get a syntax error, hence I’m really not sure what would be the correct use for my case.

5

Hi! I’m on community version, so I assume I can’t use index aggregation, but the WITH operator seems to be exactly what I needed. Thank you!

6

syntax is:

FROM collection
LET x = …

LET is after FROM, JOIN it can be chained and can be used in WHERE, group, projection, order by.
Complex expression (including subquery) evaluated every row once.

using CE, use following query (DISTINCT vs GROUP)
index aggregation unique value GROUP can perform better
query DISTINCT can perform better due to stream

  (SELECT DISTINCT RAW p.name
                            FROM veronacard.veronacard_db.mini_POI_db AS p
                            WHERE p.name IS NOT NULL)