N1QL Group by clause alias

SQL++
,
1

Hi,

I’m new to N1QL queries and I’m trying to achieve something specific. Getting some documents sorted by groups.
What I would like is something like this :

"result": {
    "Age_minus_5": {
        "origin_1": 15,
        "origin_2": 6,
        "origin_3": 4
    },
    "Age_plus_5": {
        "origin_1": 34,
        "origin_2": 10,
        "origin_3": 1
    }
}

The result of this request groups persons that has an event with a value = “new” (see request below) and sort them first by their ages (<= 5, > 5) and then by their origin and finally count them.

I achieved to do a request like this :

SELECT COUNT(*) as count, age_minus_5, age_plus_5, person.origin
FROM MY_BUCKET event 
UNNEST event.values value
JOIN MY_BUCKET person ON KEYS event.personId
WHERE 
		event.`type` = "EVENT"
	        AND value.`value` = "new"
GROUP BY 
	DATE_DIFF_MILLIS(NOW_MILLIS(), person.birthDate, "year") <= 5,
	person.origin	
		LETTING age_minus_5 = DATE_DIFF_MILLIS(NOW_MILLIS(), person.birthDate, "year") <= 5,
			age_plus_5 = DATE_DIFF_MILLIS(NOW_MILLIS(), person.birthDate, "year") > 5

But this is how the result of my request looks like :

"results": [
    {
      "age_minus_5": true,
      "age_plus_5": false,
      "count": 15,
      "origin": "origin_1"
    },
    {
      "age_minus_5": true,
      "age_plus_5": false,
      "count": 6,
      "origin": "origin_2"
    },
    {
      "age_minus_5": true,
      "age_plus_5": false,
      "count": 4,
      "origin": "origin_3"
    },
    {
      "age_minus_5": false,
      "age_plus_5": true,
      "count": 34,
      "origin": "origin_1"
    },
    {
      "age_minus_5": false,
      "age_plus_5": true,
      "count": 10,
      "origin": "origin_2"
    } ,
    {
      "age_minus_5": false,
      "age_plus_5": true,
      "count": 1,
      "origin": "origin_3"
    }

Is their a way to achieve what am I trying to do? I think that a hint could be the alias of the GROUP BY clause set by the LETTING clause but I’m not sure what to do with it.

2

hi @martin.hogge,
From your query, I guess docs have following structure:

insert into tmp (key, value) values 
("e1", { "type" : "EVENT", "vals" : ["new", "old", "new2"], "personId" : "p1"}),
("e2", { "type" : "EVENT", "vals" : ["old", "new2"], "personId" : "p2"}),
("p1", { "type" : "PERSON", "age" : 2, "origin" : "origin_1"}),
("p2", { "type" : "PERSON", "age" : 7, "origin" : "origin_2"}) ) returning meta().id, *;

With couchbase4.5, following query gives something like you want (not in the exact format though).

select array_agg(plus5) as Age_plus_5 from 
(select p.origin, count(*) as count from default e
join default p on keys e.personId
where p.age >= 5  group by p.origin) as plus5
UNION ALL
select (array_agg(minus5)) as Age_minus_5 from 
(select p.origin, count(*) as count from default e
join default p on keys e.personId
where p.age < 5 group by p.origin) as minus5;
[
  {
    "Age_minus_5": [
      {
        "count": 1,
        "origin": "origin_2"
      },
      {
        "count": 2,
        "origin": "origin_1"
      },
      {
        "count": 3,
        "origin": "origin_3"
      }
    ]
  },
  {
    "Age_plus_5": [
      {
        "count": 3,
        "origin": "origin_2"
      },
      {
        "count": 3,
        "origin": "origin_3"
      }
    ]
  }
]

In the upcoming 4.5.1 release, we made improvements to dynamically eval expressions for keys/names when constructing an object (MB-19809). Following 4.5.1 query can produce result closer to the format you want (I wrote it li’l differently than the 4.5 version, to show another flavor :slight_smile: -)

select array_agg(t.plus5) as Age_plus_5, array_agg(t.minus5) as Age_minus_5
from 
(select { p1.origin : count(*)} as plus5 from default e1
join default p1 on keys e1.personId
where p1.age >= 5  group by p1.origin
UNION
select { p2.origin : count(*)} as minus5 from default e2
join default p2 on keys e2.personId
where p2.age < 5  group by p2.origin) as t ;
[
  {
    "Age_minus_5": [
      {
        "origin_1": 2
      },
      {
        "origin_2": 1
      },
      {
        "origin_3": 3
      }
    ],
    "Age_plus_5": [
      {
        "origin_2": 3
      },
      {
        "origin_3": 3
      }
    ]
  }
]

Hope this helps
-Prasad