Select a generated field based on an array field

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"
	},
]
SELECT d.fname, 
       d.lname,
       CASE WHEN cntObj.adultChildren > 0 THEN "YES ADULT CHILDREN"
            WHEN cntObj.childrenCount > 0 TEHN "NO ADULT CHILDREN"
            ELSE "FREE" END AS status
FROM default AS d
LET cntObj = (SELECT COUNT(1) AS childrenCount, 
                     SUM(CASE WHEN c.age > 18 THEN 1 ELSE 0 END) AS adultChildren
              FROM d.children AS c)[0];

JSON doesn’t have schema the (i.e. bucket name or alias) alias, CTE , LET, LETTING variables in the hierarchy (i.e current, parent scopes) must be uniquely defined.
In your case the following causing issue. MB-43731

FROM t.children AS t1
    LET numberChildren = (SELECT RAW COUNT(*) FROM t1),

TO

 FROM t.children AS t1
    LET numberChildren = (SELECT RAW COUNT(*) FROM t1 AS t2 ),
    adultChildrenCount = (SELECT RAW COUNT(*) FROM t1  AS t3 WHERE age > 18)