SELECT 2 ARRAY_AGG in 1 query

SQL++
1

hi, everyone
i have problem with my query, i want select with 2 ARRAY_AGG in 1 query. i have 4 document like this.
province
{
_id : 1,
_type : Province,
name : DKI jakarta
}

city
{
_id : 1,
_type : City,
name : jakarta,
province : {
$ref : 1
}
}

school
{
_id : 1,
_type : School
name : pahut,
city : {
$ref : 1
}
}

student
{
_id : 1,
_type : Student,
name : bryan,
school : {
$ref : 1
}
}
{
_id : 2,
_type : Student,
name : cindy,
school : {
$ref : 1
}
}

so i want join that’s all, and count the students, so i want that’s like this :
{
province_id : 1,
name : DKI jakarta,
city : {
city_id : 1,
name : jakarta,
school : {
shcool_id : 1,
name : pahut,
total_students : 2
}
}
}

i have try query like this:
queryPrep = “SELECT d.province_id, d.name, ARRAY_AGG({d.city_id, d.nm, ARRAY_AGG({d.school_id, d.nms, d.total}) AS school FROM (”
queryPrep += “SELECT t.province_id, t.name, t.city_id, t.nm, t.school_id, t.nms, COUNT(t.student_id) AS total FROM (”
queryPrep += "select p._id province_id, p.name, m._id city_id, m.name nm, mc._id school_id, mc.name nms, dd._id student_id "
queryPrep += "from " + skCbBucket + " p "
queryPrep += "join " + skCbBucket + " m on key m.province.$ref for p "
queryPrep += "join " + skCbBucket + " mc on key mc.city.$ref for m "
queryPrep += "join " + skCbBucket + " dd on key dd.school.$ref for mc "
queryPrep += "where p._type=‘Province’ "
queryPrep += "and (m._type=‘City’ or m is missing) "
queryPrep += "and (mc._type=‘School’ or mc is missing) "
queryPrep += "and (l._type=‘Student’ or mc is missing) "
queryPrep += "UNION ALL "
queryPrep += "select p._id province_id, p.name "
queryPrep += "from " + skCbBucket + " as p "
queryPrep += "WHERE p._type = ‘Province’ "
queryPrep += ") as t "
queryPrep += "GROUP BY t.province_id, t.name, t.city_id, t.nm, t.school_id, t.nms) AS d "
queryPrep += "GROUP BY d.province_id, d.name, d.city_id, d.school_id) AS f "
queryPrep += “GROUP BY f.province_id, f.name;”

and i got an error, so what must i do ? please help me

Thanks

2

Hi @m.hilmy007,

Try the query adhoc via workbench first.
What error are you getting?
have you tried using backquote (`) around $ref?

3

yes, i already try it and im using backquote() then i got error : [3000] Object student missing name or value: array_agg({"school_id": (d.school_id), "nms": (d.nms), "total": (d.total`)}) - at AS
syntax error - at AS

4

Please provide plain SQL so that easy to read (NOT adhoc query).

You can’t have NESTED aggregates, you need to rewrite this as subquery if you need nested aggregates.
During OBJECT construction expression can’t result in the field name you need to provide explicit name.
Example : ARRAY_AGG({d.city_id, d.nm, “xxx”:ARRAY_AGG({d.school_id, d.nms, d.total})…
Also check the OBJECT construction is not complete. Error message says AS is not allowed in inside OBJECT construction. AS can be used during projection alias or from clause alias.

SELECT f.province_id, f.name, ARRAY_AGG({f.city_id, f.nm, f.schools}) AS cities FROM (
       SELECT d.province_id, d.name, d.city_id, d.nm, ARRAY_AGG({d.school_id, d.nms, d.total}) AS schools FROM (
              SELECT t.province_id, t.name, t.city_id, t.nm, t.school_id, t.nms, COUNT(t.student_id) AS total FROM (
                    SELECT p._id province_id, p.name, m._id city_id, m.name nm, mc._id school_id, mc.name nms, dd._id student_id
                    FROM default p
                    LEFT JOIN default m ON KEY m.province.`$ref` FOR p
                    JOIN default mc ON KEY mc.city.`$ref` FOR m
                    JOIN default dd ON KEY dd.school.`$ref` FOR mc
                    WHERE p._type = "Province"
                        AND (m IS MISSING OR m._type = "City")
                        AND (mc IS MISSING OR mc._type = "School")
                        AND (dd IS MISSING OR dd._type = "Student")
                    ) AS t
              GROUP BY t.province_id, t.name, t.city_id, t.nm, t.school_id, t.nms) AS d
       GROUP BY d.province_id, d.name, d.city_id, d.nm) AS f
GROUP BY f.province_id, f.name;
2 Likes
5

thanks bro its work for me.

1 Like