Please help with n1ql

SQL++
,
1

Hi Team ,

I have a database where i have the index on the type of document.
I have a requirement like this

Select data.prodId , data.compId , vwTm ,a.*
from api_external a
where type = "prodVw" 
group by data.prodId , data.compId 
having vwTm = ( select min(vwTm) from api_external b  where b.data.prodId = a.data.prodId  and b.data.compId = a.data.prodId )

Means want to select all document attributes where viewtime is minimum for a prodID and compId.The thing is we need the rest of document attributes as well. The a.* in above n1ql is not allowed as its not part of group by clause so wont work
Is there a easy way to do it ?
. How do we write this query ?

Thanks a lot for your time

Thanks

2

How about something like:

SELECT a.*
FROM api_external a
WHERE type = "prodVw"
AND vwTm = (SELECT RAW min(vwTm)
            FROM api_external b
            WHERE b.data.prodId = a.data.prodId
            AND b.data.compId = a.data.compId
           )[0]

I can’t see why you’d need grouping - your correlated sub-query takes care of picking the minimum vwTm value for the prodId/compId combination and you’re only selecting on type + minimum.

HTH.

3

Thanks
Is there a simpler way to do this other than this complicated sub query ?

4

How is the sub-query complicated? It is the same as yours just with the necessary indication for use in an equality filter - namely you only want the data not the object (RAW) and you’re only interested in the first result (array sub-script [0]). (You could also add a LIMIT 1 to it to further ensure there is no wasted processing.)

And yes, you should have an index on the two fields for that to be efficient. And you can include the type = “prodVw” filter there too. [Edit: And should be supported by an index.]

You alternatively simply order by prodId, compId, vwTm and have the application pick the first result each time prodId or compId changes.

HTH.

5

On recent versions, this:

SELECT FIRST z.a
       FOR z IN x
       WHEN z.a.vwTm = ARRAY_MIN(ARRAY y.a.vwTm FOR y IN x END)
       END.*
FROM api_external a
WHERE type = "prodVw"
GROUP BY a.data.prodId, a.data.compId
GROUP AS x
ORDER BY a.data.prodId, a.data.compId. a.vwTm

should also return what you’re looking for without a sub-query.

I’m sure there are other ways too.

HTH.

[Edit: I should note that this wouldn’t be suitable for large data sets.]

6 
CREATE INDEX ix1 ON api_external ( data.compId, data.prodId , vmTm) WHERE type = "prodVw" ;
WITH docs AS (SELECT RAW MIN([a.vwTm, MTEA(a).id])[1]
                         FROM api_external AS a
                         WHERE a.type = "prodVw" AND a.data.compId IS NOT NULL
                         GROUP BY a.data.prodId , a.data.compId )
SELECT a.* FROM api_external AS a USE KEYS docs;

If too many documents instead of CTE, use JOIN

7

If you are not going to create an index to support this (inadvisable), then building on @vsr1’s answer, you could just use:

SELECT MIN([a.vwTm,a])[1].*                                                                                                         
FROM api_external a
WHERE type = "prodVw"
GROUP BY a.data.prodId, a.data.compId
;

but it wouldn’t perform as well as every document would be fetched even though only the minimum vwTm associated one is returned. (It might be acceptable for very small datasets.)

HTH.

1 Like
8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.