Problem with Array Indexing

SQL++
1

Hello,

in our database we have many documents of the form:
[
{
“someTransaction_beneficiaryId”: 661125,
“someTransaction_creationDate”: 1451898654000,
“someTransaction_originalId”: “20160104-ZQJXGT”,
“someTransaction_statusHistory”: [
{“createdAt”: 1451898654000, “status”: “PAYMENT_AWAITING_FUNDS”},
{“createdAt”: 1451898772000, “status”: “IN_PROGRESS_TO_PROCESS”},
{“createdAt”: 1451899558000, “status”: “IN_PROGRESS_CURRENCY_TRADED”},
{“createdAt”: 1451899558000, “status”: “IN_PROGRESS_PAYER_RECEIVED_ORDER”},
{“createdAt”: 1451900718000, “status”: “PAID_AVAILABLE_FOR_PICK_UP” },
{“createdAt”: 1451999715000, “status”: “CLOSED_PAID_OUT”}
],
“type”: “someTransaction”
}

I have tried to create an Arrayed Index on theses types of documents using the following INDEX:

CREATE INDEX status_history_idx ON data (DISTINCT ARRAY sh.status FOR sh IN someTransaction_statusHistory END) WHERE type = “someTransaction”

However when I try to run a script along the lines of:

SELECT (DISTINCT ARRAY sh.status FOR sh IN someTransaction_statusHistory END) FROM data
WHERE type = “someTransaction”,

it doesn’t use the index. In fact I tried using USE INDEX in order to get N1QL to use this index instead of the other ones that I have on the bucket but it didn’t work.

I have no idea what I’m doing wrong. Any help would be greatly appreciated. Thanks.

2

Hello Mike

Your index is absolutely correct.
But your query syntax is not correct.
Please use the below query syntax:

explain SELECT sh.status FROM data where any sh in someTransaction_statusHistory satisfies sh.status = “PAID_AVAILABLE_FOR_PICK_UP” end and type = “someTransaction”;

This query uses your above created array index.
You can use any value for :
satisfies
sh.status = < “PAYMENT_AWAITING_FUNDS” >
sh.status = <“IN_PROGRESS_TO_PROCESS”>…
from the data in “someTransaction_statusHistory” array.
Great job on creating the right array index.

1 Like
3

Hi Prerna,

I tried using the query that you provided and it is still not using the proper index. In fact the query without EXPLAIN actually just returns a bunch of empty objects:

I’m really not sure where the mistake is. Could it be on my end or could it still be the query?

5

Hi Mike

Please make sure the double quotes in type = “someTransaction”; in explain query and create index are correct.
Otherwise the index will not work.
Can you please tell me your create index and explain here again?

Thanks
Prerna

6

Hi Prerna,

Yes! Thank you so much! I think that the quotation marks were the problem. I remember that I wrote the query elsewhere and copied it into n1ql. I have now changed it and the queries are using the proper index.

However the query (from above):

SELECT sh.status FROM data where any sh in someTransaction_statusHistory satisfies sh.status = “PAID_AVAILABLE_FOR_PICK_UP” end and type = “someTransaction”;

still only returns a bunch of empty objects. It isn’t as important as getting the index to work but I am curious why this doesn’t return anything.

Thank you again for the help.

Cheers

Mike

1 Like
7

Hi Mike

Am glad that your query uses the right index now.
Now if you see someTransaction_statusHistory is an array, so in order to get values for status, we need a query like:
SELECT someTransaction_statusHistory[index].status FROM default where any sh in someTransaction_statusHistory satisfies sh.status = “PAID_AVAILABLE_FOR_PICK_UP” end and
type = “someTransaction”;

where index can be any value from [0,5] ( 0 and 5 inclusive ).
An example:
SELECT someTransaction_statusHistory[4].status FROM default where any sh in someTransaction_statusHistory satisfies sh.status = “PAID_AVAILABLE_FOR_PICK_UP” end and
type = “someTransaction”;

But our query tutorial provides you a better way to loop through these indexes.

Hence you can issue the following query to get all status:

SELECT ARRAY child.status for child in default.someTransaction_statusHistory END AS status FROM default where any sh in someTransaction_statusHistory satisfies sh.status = “PAID_AVAILABLE_FOR_PICK_UP” end and
type = “someTransaction”;

Result:
[
{
“status”: [
“PAYMENT_AWAITING_FUNDS”,
“IN_PROGRESS_TO_PROCESS”,
“IN_PROGRESS_CURRENCY_TRADED”,
“IN_PROGRESS_PAYER_RECEIVED_ORDER”,
“PAID_AVAILABLE_FOR_PICK_UP”,
“CLOSED_PAID_OUT”
]
}
]

You can also replace status with createdAt.

Here is the link to our awesome query tutorial if you want to refer it for any syntax:
http://query.pub.couchbase.com/tutorial/#22

Thanks
Prerna

1 Like
8

Thank you for the link, that is very useful.