[enhance] add sort by specified field in ARRAY_SORT

SQL++
1

for example, I have the data as following

{
  "name":"name1",
  "fields":[
  {
    "type":"text",
    "value":"value1",
    "seq":"seq-2"
  },
  {
    "type":"int",
    "value":1,
    "seq":"seq-1"
  },
  {
    "type":"text",
    "value":"value2",
    "seq":"seq-3"
  }]
}

I want to get the data order by fields[*].seq, for example

{
  "name":"name1",
  "fields":[
  {
    "type":"int",
    "value":1,
    "seq":"seq-1"
  },
  {
    "type":"text",
    "value":"value1",
    "seq":"seq-2"
  },
  {
    "type":"text",
    "value":"value2",
    "seq":"seq-3"
  }]
}

further more, it will be very useful for order aggregating data.

2

SUB Query expression(SUBQ Nested Collections) provides much more enhanced operations (SUB query form clause references parent expression).
SELECT d.name , (SELECT RAW f FROM d.fields AS f ORDER BY f.seq) AS fields FROM default d;

3

How about order aggregating data? for example the following data

INSERT INTO default VALUES("test:n1",{"type":"test","name":"name1","seq":"c","key1":"value1"});
INSERT INTO default VALUES("test:n2",{"type":"test","name":"name1","seq":"b","key1":"value2"});
INSERT INTO default VALUES("test:n3",{"type":"test","name":"name1","seq":"d","key1":"value3"});
INSERT INTO default VALUES("test:n4",{"type":"test","name":"name1","seq":"a","key1":"value4"});
INSERT INTO default VALUES("test:n5",{"type":"test","name":"name2","seq":"b","key1":"value5"});
INSERT INTO default VALUES("test:n6",{"type":"test","name":"name2","seq":"a","key1":"value6"});

I want to get the following data group by name,and order by seq for aggregating data.

[
  {
    "a_list": [
      {
        "key1": "value4",
        "seq": "a"
      },
      {
        "key1": "value2",
        "seq": "b"
      },
      {
        "key1": "value1",
        "seq": "c"
      },
      {
        "key1": "value3",
        "seq": "d"
      }
    ],
    "name": "name1"
  },
  {
    "a_list": [
      {
        "key1": "value6",
        "seq": "a"
      },
      {
        "key1": "value5",
        "seq": "b"
      }
    ],
    "name": "name2"
  }
]
4 
SELECT d.name , (SELECT RAW f FROM d.a_list AS f ORDER BY f.seq) AS a_list FROM (
         SELECT d.name , ARRAY_AGG({d.key1,d.seq})  AS a_list FROM default d GROUP BY d.name) AS d;

ON array elements You can filter, do aggrgates, LIMIT, OFFSET, ORDER etc (i.e basically you can do all select features) without doing scan or fetch. Can be used in UPDATE/DELETE/MERGE too.

https://developer.couchbase.com/documentation/server/4.6/n1ql/n1ql-language-reference/correlated-subqueries.html
https://developer.couchbase.com/documentation/server/4.6/n1ql/n1ql-language-reference/subquery-examples.html

2 Likes
5

Thank you very much.

6

refer to https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/subqueries.html

https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/from.html

7

Thank you very much, it is very useful.