How can I sort json_object inside json_group_array ?

[chrg1001]

Hi, I am new to drift.

I have a few questions around the json extension.

Table definition

class Company extends Table {
  @override
  Set<Column> get primaryKey => {id};

  TextColumn get id => text()();

  TextColumn get name => text()();
}

class Employee extends Table {
  @override
  Set<Column> get primaryKey => {id};

  TextColumn get id => text()();

  TextColumn get companyId => text().references(Company, #id)();

  TextColumn get name => text()();
}

class Project extends Table {
  @override
  Set<Column> get primaryKey => {id};

  TextColumn get id => text()();

  TextColumn get name => text()();
}

class ProjectMember extends Table {
  @override
  Set<Column> get primaryKey => {projectId, employeeId};

  TextColumn get projectId => text().references(Project, #id)();

  TextColumn get employeeId => text().references(Employee, #id)();

  TextColumn get role => textEnum<ProjectMemberRole>()();
}

What result I want

[
  {
    "id": "1",
    "name": "project-x",
    "projectMembers": [
      {
        "employeee": {
          "id": "3",
          "companyId": "70",
          "name": "Alex"
        },
        "role": "manager"
      },
      {
        "employeee": {
          "id": "4",
          "companyId": "71",
          "name": "Rob"
        },
        "role": "engineer"
      },
    ]
  }
]

I think the following SQL will produce the above results.

SELECT
  "project"."id" AS "project.id",
  "project"."name" AS "project.name",
  json_group_array (
    json_object (
      "role", "project_member"."role",
      "employee", (
        SELECT
          json_object (
            "id", "employee"."id",
            "companyId", "employee"."company_id",
            "name", "employee"."name"
          )
        FROM
          "project_member"
          INNER JOIN "employee" ON "employee"."id" = "project_member"."employee_id"
      )
    )
    ORDER BY
      json_extract (employee, '$.name') ASC
  ) AS "project.members"
FROM
  "project"
  INNER JOIN "project_member" ON "project_member"."project_id" = "project"."id"
WHERE
  "project"."id" = ?;

So, I defined the json_object function based on this and wrote the following Dart code.

Expression<T> jsonObject<T extends Object>(
  Map<Expression<String>, Expression> values,
) {
  return FunctionCallExpression<T>('json_object', _jsonObjectArgs(values));
}

List<Expression> _jsonObjectArgs(
  Map<Expression<String>, Expression> values,
) {
  final expressions = <Expression>[];
  for (final MapEntry(:key, :value) in values.entries) {
    expressions.add(key);
    expressions.add(value);
  }
  return expressions;
}

...

final employeeJsonObject = jsonObject(
  employee.columnsByName.map(
    (key, value) => MapEntry(
      Constant(key),
      value,
    ),
  ),
);

final projectMemberSubquery = subqueryExpression(
 projectMember.selectOnly().join([
    innerJoin(
      employee,
      employee.id.equalsExp(
        projectMember.employeeId,
      ),
    ),
  ])
    ..addColumns([employeeJsonObject]),
);

final projectMembersJsonGroupArray = jsonGroupArray(
  jsonObject({
    const Constant('role'): projectMember.role,
    const Constant('member'): projectMemberSubquery,
  }),
  orderBy: OrderBy([
    OrderingTerm(
      expression: "**how can I write expression here?**",
      mode: OrderingMode.asc,
    ),
  ]),
);

final statement = select(project)
    .addColumns([projectMembersJsonGroupArray]).join([
  innerJoin(
    projectMember,
    projectMember.projectId.equalsExp(
      project.id,
    ),
  ),
]);

print(statement.constructQuery().sql);

Questions

1. How can I write the expression of OrderingTerm?
2. Since I usually write SQL using a TypeScript QueryBuilder called kysely, I am tempted to write it using the json function like this, but is there an easy way to get the above results without bothering to use the json function? (Simply use leftJoin to get the data in one dimension, and then merge them on the Dart side?)

My goal is not to use the json function, but to get the result I want (preferably in a concise way), so if you have any other good ideas, I'd love to know! (However, I would like to avoid (in a sense) denormalizing the data structure, e.g., having the ProjectMember itself as a JSON string and having it as a string column in the Project table.)

[simolus3]

How can I write the expression of OrderingTerm?

In the latest drift version, jsonGroupArray is part of the package and has an orderBy argument.

(Simply use leftJoin to get the data in one dimension, and then merge them on the Dart side?)

I think traditionally the way you'd write this is to first select the projects you're interested in. Then you collect their ids and run a second query filtering members and employees involved in that particular project. We have an example for that here. Using a single join to fetch everything and then filter out duplicates in the project columns would also work.

[chrg1001]

@simolus3

Thank you for your reply.

I have also checked this thread and am aware that orderBy is available.
My problem was that I could not figure out how to write json_extract in the expression argument of that orderBy.

I thought I could write something like the following, but is this proper usage of Constant?

...
const jsonObjectKeyMember = Constant('member');

final projectMembersJsonGroupArray = jsonGroupArray(
  jsonObject({
    const Constant('role'): projectMember.role,
   jsonObjectKeyMember: projectMemberSubquery,
  }),
  orderBy: OrderBy([
    OrderingTerm(
      expression: jsonObjectKeyMember.jsonExtract(r'$. name'),
      mode: OrderingMode.asc,
    ),
  ]),
);
...

[simolus3]

That's a correct use of Constant, yes. It will compile to json_group_array('role', members.role, 'member', (SELECT ...)) in SQL which should give you the correct object.

[chrg1001]

Thank you very much!