可以使用 to_json 砖厂的自定义项。构建jar文件后，可以添加jar并创建临时函数，如下所示：

add jar /path/brickhouse-0.7.0-SNAPSHOT.jar;

CREATE TEMPORARY FUNCTION to_json AS 'brickhouse.udf.json.ToJsonUDF';

我用你提供的样本数据测试了udf。

describe table_a;
col_1                   string                  None                
col_2                   int                     None                

describe table_b; 
col_1                   string                  None                
col_2                   string                  None                
col_3                   int                     None                

select * from table_a;
userLogins  30

select * from table_b;
userLogins  Site A  10
userLogins  Site B  20

select
to_json(named_struct( 'name', a.col_1, 'children' , array(named_struct('name', b.col_2, 'logins', b.col_3))))
from table_a a
join table_b b
on a.col_1 = b.col_1;

{"name":"userLogins","children":[{"name":"Site B","logins":20}]}
{"name":"userLogins","children":[{"name":"Site A","logins":10}]}

你可以从brickhouse博客中找到更多关于udf用法的细节。

我想你是在找砖厂的自选基金。

select named_struct(
    'name', b.col_1, 
    'children',  collect(named_struct('name', b.col_2, 'logins', b.col_3))) 
from table_a a join table_b b
on a.col_1 = b.col_1
group by b.col_1;

上面输出下面的json

{"name":"userLogins","children":[{"name":"Site A","logins":10},{"name":"Site B","logins":20}]}