Iteration on array using index on N1QL

SQL++
1

Hi all,
I need to create a N1QL query (for N1QL query and N1QL analytics) in order to compare an array element with the previous array element. For example. if I have this array [1,5,7,10] I want to obtain the subtraction of an element and the previous one. So the result should be [4,2,3] (Array [1] -Array [0], Array [2] -Array [1], Array [3] -Array [2]). Obviously, the array length cloud change.
Thanks a lot in advance.

2

N1QL query

SELECT ARRAY v2-v1 FOR v1 IN [1,5,7,10] , v2 IN [1,5,7,10][1:] END;

SELECT ARRAY v2- [1,5,7,10][pos] FOR pos:v2 IN [1,5,7,10][1:] END;

3

Thanks a lot vsr1, as usual. I got a near solution to yours, but when I try to do the same on analytics with SELECT VALUE v2-v1 FROM [1,5,7,10] v1, [1,5,7,10] [1:] v2 I get a join between all the element subtraction:. [ 4, 6, 9, 0, 2, 5, -2, 0, 3, -5, -3, 0], not only the subtracting of the previous one from the selected element.

4

Assuming your array is sorted.

SELECT VALUE v2 - (SELECT VALUE v1 FROM [1,5,7,10] v1 WHERE v1 < v2 ORDER BY v1 DESC LIMIT 1)[0] FROM [1,5,7,10][1:] v2

6.5.0:

SELECT VALUE (SELECT VALUE v1 - FIRST_VALUE(v1) OVER(ORDER BY v1 ROWS 1 PRECEDING) FROM [1,5,7,10] v1)[1:];