Return documents as an array in a single query

SQL++
1

Sorry for the confusing title but i dont know how to put it exactly (please feel free to edit). I have this documents. I didn’t try anything as I am clueless nor I am sure what i want could be achieved.

I am in couchbaselite 2.8.0 (couldn’t install 2.8.1) in Java envrionment.

{villageID: 1, groupID: 1, familyID: 1, docType:'f'},
{villageID: 1, groupID: 1, docType:'g'},
{villageID: 2, groupID: 3, familyID: 2, docType:'f'},
{villageID: 2, groupID: 3, docType:'g'},

in traditional database, the schema would look like this:

villages
villageID

groups
groupID
villageID

families
familyID
groupID

Basically, what I want is to return list of groups and families grouped by village. I don’t know if it is possible but the output to be something like this:

villages:{id:1, groups:[ {villageID: 1, groupID: 1, docType:'g'},], families: [ {villageID: 1, groupID: 1, familyID: 1, docType:'f'},]}

2 
SELECT d.villageID AS id, 
       ARRAY_AGG(CASE WHEN d.docType = "g" THEN d END) AS groups,
       ARRAY_AGG(CASE WHEN d.docType = "f" THEN d END) AS familes,
FROM default AS d
WHERE ...
GROUP BY d.villageID;

OR

SELECT d1.id, OBJECT v.docType: v.arr FOR v IN av END.*
FROM (SELECT d.villageID AS id, d.docType
             ARRAY_AGG(d) AS arr
      FROM default AS d
      WHERE ...
     GROUP BY d.villageID, d.docType ) AS d1
GROUP BY d1.id
LETTEING av = ARRAY_AGG ({d1.docType, d1.arr});