SQL group by values stored in arrays

Question

I use OrientDB. I have a table like this:

 NAME | CATEGORIES
-------------------
  N1  |   [A,B]
  N2  |   [C]
  N3  |   [C,A]
  N4  |   [A,B]

And I would like to build a query that returns a list of categories, and for each category a list of related names, like this:

 CATEGORY |   NAMES
-----------------------
    A     | [N1,N3,N4]
    B     | [N1,N4]
    C     | [N2,N3]

If "categories" wasn't an array, I could achieve it with:

SELECT
  Categories as Category,
  set(Name) as Names
FROM Table
GROUP BY Categories

But, being Categories arrays, this is what I get:

 CATEGORY |  NAMES
--------------------
   [A,B]  | [N1,N4]
    [C]   | [N2]
   [C,A]  | [N3]

What query should I rather write?

Answer 1

I reproduced your structure with this command

create class test extends v
create property test.name string
create property test.categories embeddelist string

insert into test(name,categories) values ("N1",["A","B"]),("N2",["C"]),("N3",["C","A"]),("N4",["A","B"])

and I used this query

select categories,$a.name as name from (select distinct(categories) as categories from (select categories  from test order by categories unwind categories))
let $a = (select name from test where categories contains $parent.$current.categories)

Hope it helps.