How can I use json_group_array & json_object functions?

[hamishjohnson]

I am joining a 1 to many table onto the main table with outer join. I've got the join right but I'd like to return the other table in an array of json object.

I've got this working where it's only adding the ids to the column

    final tagsGroupConcat = db.tagRows.id.groupConcat(separator: ', ');
    // A few moments later
    ..addColumns([
      tagsGroupConcat,
    ])

But I'd like to join multiple params from the column in such a way:

  SELECT  
         json_group_array(
           json_object(
             'tag_id', t.id, 
             'tag_color', t.color, 
           )
         )
FROM ...

[simolus3]

There's no query builder API for these functions yet, but you can use a FunctionCallExpression to call them. But I think it would be useful to have them available, I'll add them to drift.

[hamishjohnson]

good to know - i did see mention of json_group_array in the code so I did wonder if there was some way of calling it even if it's not part of the API.

This is a very useful part of the API to know, but it's not in the documentation

[hamishjohnson]

And thanks!