Can I use GROUP BY without using aggregate function in analytics?

Analytics
, ,
1

Hi Guys,

 Is there a way in analytics I can use GROUP BY without using aggregate function ?

Here is my location document:

[

 {
    "location": "location::0032",
    "oldRank": {
      "name": "D-2371",
      "level": "D",
      "position": 2371
    },
    "territory": "territory::1000"
  },
  {
    "location": "location::0033",
    "oldRank": {
      "name": "D-2376",
      "level": "D",
      "position": 2376
    },
    "territory": "territory::1000"
  },
  {
    "location": "location::2233",
    "oldRank": {
      "name": "C-1376",
      "level": "C",
      "position": 1376
    },
    "territory": "territory::0007"
  },
    {
    "location": "location::0001",
    "oldRank": {
      "name": "C-1376",
      "level": "C",
      "position": 1376
    },
    "territory": "territory::0004"
  }

My expected result would be like this:

[
 {
    "territory": "territory::1000",
    "locations": [
    	{
    	  "location": "location::0032",
        "oldRank": {
             "name": "D-2371",
            "level": "D",
            "position": 2371
          }
    	},
    	{
    		"location": "location::0033",
             "oldRank": {
             "name": "D-2376",
            "level": "D",
            "position": 2376
          }
    	}
     ]
  },
 {
    "territory": "territory::0007",
    "locations": [
    	{
    	  "location": "location::2233",
    	  "oldRank": {
		     "name": "C-1376",
		     "level": "C",
		     "position": 1376
           }
    	}
    ]
  },
 {
    "territory": "territory::0004",
    "locations": [
    	{
    	  "location": "location::0001",
    	  "oldRank": {
		     "name": "C-1376",
		     "level": "C",
		     "position": 1376
           }
    	}
    ]
  }
]

My Initial query:

SELECT
  meta(l).`id` as location,
  l.`rank` as oldRank,
  l.territory as territory
FROM
company_analytics.`locations` l
WHERE l.`status` = 'active'

Thanks guys,

2

Hi @lyndondonz,

Yes, you can use GROUP BY without an aggregate function. Here’s the query:

select territory, (select gi.l.location, gi.l.oldRank from g as gi) as locations
from company_analytics.locations l
group by territory group as g
3

Hi @dmitry.lychagin,
It’s the query above doesn’t produce any data just an empty object {} :slight_smile:

4

Hi @dmitry.lychagin,

Sorry, I made it work here the query:

SELECT
  l.territory, 
  (SELECT g.l.rank AS oldRank FROM g) AS locations
FROM `bluebonnet_analytics`.`locations` AS l
GROUP BY l.territory
GROUP AS g

But, How can I include the docId (meta().id) in the statement?:

(SELECT g.l.rank AS oldRank FROM g) AS locations

thanks man, I really need help for this :frowning:

5

Hi @lyndondonz,

You can assign it to a variable using a LET clause, then access it in the subquery as follows:

SELECT
  l.territory, 
  (SELECT g.l.rank AS oldRank, g.m AS location FROM g) AS locations
FROM `bluebonnet_analytics`.`locations` AS l
LET m = META().id
GROUP BY l.territory
GROUP AS g
1 Like