How to get array positions at doc using N1QL?

SQL++
1

Suppose I have a doc similar to:

{
“travel-sample”: {
“airline”: “AF”,
“airlineid”: “airline_137”,
“destinationairport”: “CDG”,
“distance”: 8748.296323466084,
“equipment”: “772”,
“id”: 10002,
“schedule”: [
{
“day”: 0,
“flight”: “AF678”,
“utc”: “15:05:00”
},
{
“day”: 0,
“flight”: “AF392”,
“utc”: “11:04:00”
},
{
“day”: 0,
“flight”: “AF114”,
“utc”: “07:35:00”
}
],
“sourceairport”: “TNR”,
“stops”: 0,
“type”: “route”
}
}

having flight code I need to get it’s index at json array.
For example: for AF678 → 0, for AF392 → 1, for AF114 → 2.
I’m looking for some N1QL query to retrive such info.
It is required to make sub-doc mutation request on the next step:

bucket.mutateIn("route10002").replace("schedule[index].utc","07:35:00").execute();
2

You should use index and query in the Using array index to speed up update query instead of N1QL and sub-doc mutation.

SELECT FIRST ARRAY_POS(schedule, v) FOR v IN schedule WHEN  v.flight = "AF678" END
 FROM `travel-sample`  WHERE ..........

If more than one array element matches flight = “AF678” it gives first matched array position

1 Like
3

Thanks. And how can I get all indexes if more than one array element matches flight = “AF678”?

4

All matched positions are returned in array

SELECT ARRAY ARRAY_POS(schedule, v) FOR v IN schedule WHEN  v.flight = "AF678" END
 FROM `travel-sample`  WHERE ..........
5

Thx. Might you also know how to get the follows.
Let’s suppose that flight code ‘AF114’ from first json was replaced to ‘AF678’.
So now it contains two elements are matched condition flight = “AF678”.
I need to consuct n1ql request to get next structure:
[
{
“id”: “route_10002”,
“position”: 0,
“utc”: “15:05:00”
},
{
“id”: “route_10002”,
“position”: 2,
“utc”: “07:35:00”
}
]

I tried with n1ql:

SELECT meta().id, sh.utc,
FIRST ARRAY_POS(t.schedule, v) FOR v IN t.schedule WHEN  v.flight = "AF678" END AS position
FROM `travel-sample` t UNNEST t.schedule sh  
WHERE meta().id = 'route_10002' AND t.type='route' AND sh.flight = "AF678"

It produces something similar but only first array position:

I tried one more:

SELECT meta().id, 
ARRAY v.utc FOR v IN t.schedule WHEN  v.flight = "AF678" END AS utc,
ARRAY ARRAY_POS(t.schedule, v) FOR v IN t.schedule WHEN  v.flight = "AF678" END AS position
FROM `travel-sample` t  
WHERE meta().id = 'route_10002' AND t.type='route'

It produces both but at not disarable structure:

6 
SELECT meta(t).id, sh.utc, sh.position
FROM `travel-sample` AS t
UNNEST  ARRAY OBJECT_ADD(v, "position",pos ) FOR pos IN ARRAY_RANGE(0, ARRAY_LENGTH(t.schedule)) , v IN t.schedule END AS sh
WHERE meta(t).id = 'route_10002' AND t.type='route' AND sh.flight = "AF678";
1 Like
7

Out of interest; what issue do you have using Sub-Doc for this? It’ll most likely be faster than N1QL, and doesn’t require an array index to be created.

8

I needed to make multi update at docs where data is contained inside an array (route doc with schedule at travel-sample bucket as an example). I tried using-array-index-to-speed-up-update-query with array index and indeed it’s more slower than sub-doc with mutation. You are right.