Hey all,
I’m having some problems querying this case, I have these documents:
[
{
fname: "Lona",
lname: "Sanchez",
age: 25,
children: [
]
},
{
fname: "Ronie",
lname: "Flowers",
age: 30,
children: [
{
fname: "Steve",
age: "15"
},
{
fname: "Maria",
age: "18"
}
]
},
{
fname: "Cindy",
lname: "Lopez",
age: 40,
children: [
{
fname: "Steve",
age: "15"
},
{
fname: "Maria",
age: "13"
}
]
},
]
With this query I need to “generate” a new field based on the children array field, if the “user” has no children the generated status field should be “FREE”, if the “user” has one or more child with age up from 18 the status should be “YES ADULT CHILDREN” and if the user has not children with his age more than 18 years the status should be “NO ADULT CHILDREN”; but my query is displaying an error: {“code”:4020,“msg”:“Duplicate FROM expression alias t1”}
SELECT t.fname, t.lname, status
FROM tutorial AS t
LET status = (
SELECT RAW CASE numberChildren
WHEN 0 THEN "FREE"
ELSE
CASE adultChildrenCount
WHEN 0 THEN "NO ADULT CHILDREN"
ELSE "YES ADULT CHILDREN"
END
END
FROM t.children AS t1
LET numberChildren = (SELECT RAW COUNT(*) FROM t1),
adultChildrenCount = (SELECT RAW COUNT(*) FROM t1 WHERE age > 18)
);
I generate the query without the CASE, only adding to the main select the numberChildren and adultChildrenCount and it works but I need to reduce those values to only one status; so I nested the query, but I do not guess where is the issue.
The result array should be:
[
{
fname: "Lona",
lname: "Sanchez",
status: "FREE"
},
{
fname: "Ronie",
lname: "Flowers",
status: "YES ADULT CHILDREN"
},
{
fname: "Cindy",
lname: "Lopez",
status: "NO ADULT CHILDREN"
},
]