Returning the result of a subquery as a field-CodePudding

I am essentially trying to return the result of a subquery in a field of my outer query like so:

SELECT field1, field2, field8 = (SELECT * FROM TABLE)
FROM OTHERTABLE

I found an example of my intended functionality here but it doesn't seem to work on mariadb (which from what I can gather is just mysql). Is there a way to do this with mysql or am I missing something? I'm getting an "Unknown column 'field8' in 'field list'".

I'm using a node mysql module and I hope to achieve

[{
   field1: something,
   field2: somethingelse,
   field8: [{
              data, data, data
           },{
              data, data, data
           }] 
}]

If this isn't possible, I can always perform two separate queries

CodePudding user response：

Fix the syntax, and use JSON_ARRAYAGG() to convert the rowset to json:

SELECT
    field1,
    field2,
    JSON_ARRAYAGG((SELECT * FROM TABLE)) as field8
FROM OTHERTABLE
GROUP BY field1, field2