jsonGroupArray function

Expression<String> jsonGroupArray(Expression<Object> value, { OrderBy? orderBy, Expression<bool>? filter, })

Returns a JSON array containing the result of evaluating value in each row of the current group.

As an example, consider two tables with a one-to-many relationship between them:

class Emails extends Table {
  TextColumn get subject => text()();
  TextColumn get body => text()();
  IntColumn get folder => integer().references(Folders, #id)();
}

class Folders extends Table {
  IntColumn get id => integer()();
  TextColumn get title => text()();
}

With this schema, suppose we want to find the subject lines of every email in every folder. A join gets us all the information:

final query = select(folders)
  .join([innerJoin(emails, emails.folder.equalsExp(folders.id))]);

However, running this query would duplicate rows for Folders - if that table had more columns, that might not be what you want. With jsonGroupArray, it's possible to join all subjects into a single row:

final subjects = jsonGroupObject(emails.subject);
query
  ..groupBy([folders.id])
  ..addColumns([subjects]);

Running this query would return one row for each folder, where row.read(subjects) is a textual JSON representation of the subjects for all emails in that folder. This string could be turned back into a list with (json.decode(row.read(subjects)!) as List).cast<String>().

Implementation

Expression<String> jsonGroupArray(
  Expression value, {
  OrderBy? orderBy,
  Expression<bool>? filter,
}) {
  return AggregateFunctionExpression('json_group_array', [value],
      orderBy: orderBy, filter: filter);
}