Add a value of object instead of replace it

SQL++
,
1

Hello,
i have this query:

SELECT alias as doc, user as user , (OBJECT doc.type:doc FOR doc IN ARRAY_AGG(documents) END).*
FROM bucket AS alias 
LEFT OUTER JOIN bucket AS user ON Meta(user).id = alias.user_id 
LEFT OUTER JOIN bucket AS documents ON documents.duty_id = Meta(alias).id and documents.type IN ["Refuelling", "CarReport"]
WHERE omnitrace_alias.type = "Duty")
GROUP BY omnitrace_alias, user

The result that i’d like is

{
   doc: the document,
   user: the join user,
   Refuelling: [
     an array of all documents with type "Refuelling"
   ],
   CarReport: [
     an array of all documents with type "CarReport"
   ]

The problem with my query in that in OBJECT type.type: type, the field is replaced with a new item, so i ends up with just one document per type.
How can i add a doc instead of replace it?

Thanks in advance

2 
SELECT t.doc, t.user, (OBJECT doc[0].type:doc FOR doc IN ARRAY_AGG(t.av) END).*  FROM (
        SELECT alias as doc, user as user , ARRAY_AGG(documents) AS av
        FROM bucket AS alias
        LEFT OUTER JOIN bucket AS user ON Meta(user).id = alias.user_id
        LEFT OUTER JOIN bucket AS documents ON documents.duty_id = Meta(alias).id and documents.type IN ["Refuelling", "CarReport"]
        WHERE alias.type = "Duty"
        GROUP BY alias, user, documents.type) AS t
GROUP BY t.doc, t.user;
3

would it be possible to add on every documents object also the id? Having the complete object?

4 
SELECT t.doc, t.user, (OBJECT doc[0].dv.type:doc FOR doc IN ARRAY_AGG(t.av) END).*  FROM (
        SELECT alias as doc, user as user , ARRAY_AGG({"dv":documents, "id":META(documents).id}) AS av
        FROM bucket AS alias
        LEFT OUTER JOIN bucket AS user ON Meta(user).id = alias.user_id
        LEFT OUTER JOIN bucket AS documents ON documents.duty_id = Meta(alias).id and documents.type IN ["Refuelling", "CarReport"]
        WHERE alias.type = "Duty"
        GROUP BY alias, user, documents.type) AS t
GROUP BY t.doc, t.user;

OR

SELECT t.doc, t.user, (OBJECT doc[0].type:doc FOR doc IN ARRAY_AGG(t.av) END).*  FROM (
        SELECT alias as doc, user as user , ARRAY_AGG(OBJECT_PUT(documents,"id",META(documents).id)) AS av
        FROM bucket AS alias
        LEFT OUTER JOIN bucket AS user ON Meta(user).id = alias.user_id
        LEFT OUTER JOIN bucket AS documents ON documents.duty_id = Meta(alias).id and documents.type IN ["Refuelling", "CarReport"]
        WHERE alias.type = "Duty"
        GROUP BY alias, user, documents.type) AS t
GROUP BY t.doc, t.user;
1 Like