Query with UNNEST and SUM of nested elements amounts returns empty line with amount 0 event if documents not found

SQL++
1

Hello , I have a query:
SELECT
s.orderNo AS OrderId,
s.updateDate AS OrderDateTime,
SUM(eventItems.estimatedFinalPrice) AS Amount
FROM Stores s
INNER UNNEST s.orderEventItems AS eventItems
WHERE
s.type=‘order’ AND
s.orderStatus = 2 AND
s.siteId = ‘c91c6476-d620-47b0-bd4d-4ff38ae1f634’ AND
STR_TO_MILLIS(s.updateDate) >= STR_TO_MILLIS(‘2017-01-10T00:00:00.0000000Z’) AND
STR_TO_MILLIS(s.updateDate) <= STR_TO_MILLIS(‘2017-01-11T00:00:00.0000000Z’)
GROUP BY
s.orderNo,
s.updateDate

The problem is that even if no documents with type order found or no order documents found between the date it returns empty line with amount null , but we are expecting result without lines. Working as expected when at least one document found.

Do we missed something ?

Please help,
Thanks a lot.

2

Modify your GROUP BY as follows.

GROUP BY
s.orderNo,
s.updateDate
HAVING COUNT(*) > 0;