N1QL query - separate array into groups with continuously same value for a field

SQL++
1

Hello,

I have data like this.

{
            "mybucket": {
                "timecode": 1,
                "type": "test2",
                "val": 1
            }
        },
{
            "mybucket": {
                "timecode": 2,
                "type": "test2",
                "val": 1
            }
        },
{
            "mybucket": {
                "timecode": 3,
                "type": "test2",
                "val": 1
            }
        },
{
            "mybucket": {
                "timecode": 4,
                "type": "test2",
                "val": 2
            }
        },
{
            "mybucket": {
                "timecode": 5,
                "type": "test2",
                "val": 2
            }
        },
{
            "mybucket": {
                "timecode": 6,
                "type": "test2",
                "val": 1
            }
        },
{
            "mycket": {
                "timecode": 7,
                "type": "test2",
                "val": 1
            }

I want to construct N1QL query which returns the following data:
{
“mybucket”: {
“timecode”: 1,
“type”: “test2”,
“val”: 1
}
},
{
“mybucket”: {
“timecode”: 4,
“type”: “test2”,
“val”: 2
}
},
{
“mybucket”: {
“timecode”: 6,
“type”: “test2”,
“val”: 1
}
},

Basically, I need to separate the array of documents into parts with continuous value for “val” and then select the first document (ordered by timecode) from each part.

In SQL I would probably use some combination of window functions. How would I do this in N1QL? I started with trying to select previous value for “val” to know if it changed compared to previous row and go from there with this query:

SELECT
	e1,
	(SELECT e2.timecode AS prev_timecode FROM mybucket e2 USE KEYS META(e1).id WHERE e2.timecode < e1.timecode ORDER BY e2.timecode DESC LIMIT 1) x
FROM
	mybucket e1
WHERE
	e1.type="test2"
ORDER BY
	e1.timecode

But it returns empty results for x.

Can you help me with how to approach this problem using N1QL? Thank you

2

What is Couchbase Version you are using?

3

It is 4.5.1-2844 Community Edition

4 
SELECT MIN([e1.timecode,e1])[1] AS ev  FROM mybucket e1
WHERE e1.type = "test2"
GROUP BY e1.val
ORDER BY ev.val;
5

Thank you, but this doesn’t answer my question. It groups documents based on “val”, but doesn’t take into account that I want to have three groups here.

Hope this illustrates it better:

timecode: ------------------------------------>
val:           1    1    1   2    2   1   1
group:             G1           G2      G3

if I group just by “val” it produces this:

timecode: ------------------------------------>
val:           1    1    1   2    2   1   1
group:             G1           G2      G1
6

Based on what you are grouping G3. Is the data is in Array

7

The groups are continuous intervals with the same value for “val”. Intervals being based on “timecode” field.

Nope these are just documents with key test-1,test-2,test-3,… If the data would be in array sorted by “timecode”, would that help?

8

Another way to formulate this is that I need each record when a value of “val” changes from the previous record, when sorted by timecode.

9 
INSERT INTO default VALUES ("kk01",{ "timecode": 1, "type": "test2", "val": 1 }),
                    VALUES ("kk02",{ "timecode": 2, "type": "test2", "val": 1 }),
                    VALUES ("kk03",{ "timecode": 3, "type": "test2", "val": 1 }),
                    VALUES ("kk04",{ "timecode": 4, "type": "test2", "val": 2 }),
                    VALUES ("kk05",{ "timecode": 5, "type": "test2", "val": 2 }),
                    VALUES ("kk06",{ "timecode": 6, "type": "test2", "val": 1 }),
                    VALUES ("kk07",{ "timecode": 7, "type": "test2", "val": 1 });


SELECT ev FROM (SELECT RAW ARRAY v FOR v IN av WHEN ARRAY_POS(av,v) == 0 OR v.val != av[ARRAY_POS(av,v)-1].val END
                 LET av = (SELECT RAW e1 FROM default e1 WHERE e1.type = "test2" ORDER BY e1.timecode)) AS q UNNEST q AS ev;
10

Thank you very much, this works great