Union all without null data in already exist data

SQL++
,
1

Hi, i’m still newbie and i need your helps.

i have problem with my query, i have document like this :
{
“_id”: “Group-1”,
“_type”: “Group”,
“name”: “group1”
},
{
“_id”: “Group-2”,
“_type”: “Group”,
“name”: “group2”
},
{
“_id”: “Group-3”,
“_type”: “Group”,
“name”: “group3”
},
{
“_id”: “Member-1”,
“_type”: “Member”,
“name”: “jack”,
“group”: {
“$ref”:“Group-1”,
“_type”:“Group”
}
},
{
“_id”: “Member-2”,
“_type”: “Member”,
“name”: “vin”,
“group”: {
“$ref”:“Group-2”,
“_type”:“Group”
}
}

so i want union all and i’m already make the query but the results are not as I imagined. this the result i get :
[
{
“group_id” : “Group-1”,
“members” : [
{},
{
“member_id” : “Member-1”,
“name” : “jack”
}
]
},
{
“group_id” : “Group-2”,
“members” : [
{},
{
“member_id” : “Member-2”,
“name” : “vin”
}
]
},
{
“group_id” : “Group-1”,
“members” : [
{}
]
}
]

so that result was too difficult for me to manage and i want the simple result like this :
[
{
“group_id” : “Group-1”,
“members” : [
{
“member_id” : “Member-1”,
“name” : “jack”
}
]
},
{
“group_id” : “Group-2”,
“members” : [
{
“member_id” : “Member-2”,
“name” : “vin”
}
]
},
{
“group_id” : “Group-1”,
“members” : [
{}
]
}
]

i think just that my problem, hope i get a satisfying answer .

THANK YOU :slightly_smiling_face:

2

Please post your query

SELECT d.`group`.`$ref` AS group_id, ARRAY_AGG({"member_id":d._id, d.name}) AS members
FROM default AS d
WHERE d._type = "Member"
GROUP BY d.`group`.`$ref`;

If you need grouping on UNION ALL post produce UNINON ALL in parent query do GROUPING.

1 Like