ARRAY_AGG With LIMIT Not showing expected result

SQL++
1

Can we use Limit with ARRAY_AGG funciton

SELECT ARRAY_AGG(DISTINCT t.orderNumber) as orderNumbers
FROM ORDER_TRKR t
WHERE t.orderTm = 2085982157
LIMIT 5

2

There is no GROUP BY, So query produces single row.

What you looking can be achieved by

CREATE INDEX ix1 ON ORDER_TRKR(orderTm , orderNumber)

WITH orderNumbers AS (SELECT DISTINCT RAW t.orderNumber
                      FROM ORDER_TRKR  AS t
                      WHERE  t.orderTm = 2085982157
                      ORDER BY t.orderNumber ASC
                      LIMIT 5)
SELECT orderNumbers;
3

Ok got it, and somehow one of our process created a document with " B1ZAW1NR11361101103" empty space we can fetch those document by CURD operation, but when i go to bucket and search by Document ID that document does not show up. i have tried some option but not showing " Show range " option is not helping me either. Just curious how i can achieve it ?

4

It does seem to be a UI issue. <edit> No, it isn’t - there is a delay in updating as indexing is asynchronous and I was too quick in my brief test. You can still use the procedure below to change it if easier than using the UI. That said, which version are you using? </edit>

You could update it via Query -

INSERT INTO bucket (KEY k, VALUE v)
SELECT "new-key-without-space" k, bucket v FROM bucket USE KEYS["key-with-space"];

Verify the new copy is there with:

SELECT * FROM bucket USE KEYS["new-key-without-space"];

Once satisfied, delete the original:

DELETE FROM bucket USE KEYS["key-with-space"];

HTH.