function jsonArrayFrom<O>(
  expr: SelectQueryBuilder<any, any, O>
): SelectQueryBuilder<any, any, O> {
  const selection = sql`coalesce(json_arrayagg(json_object(${sql.join(
    getJsonObjectArgs(expr.toOperationNode())
  )})), '[]') `;
  return expr.clearSelect().select(selection);
}

function getJsonObjectArgs(node: SelectQueryNode): RawBuilder<unknown>[] {
  return node.selections!.flatMap(({ selection: s }) => {
    if (ReferenceNode.is(s) && ColumnNode.is(s.column)) {
      return [
        sql.lit(s.column.column.name),
        sql.id(s.table.table.identifier.name, s.column.column.name),
      ];
    } else if (ColumnNode.is(s)) {
      return [sql.lit(s.column.name), sql.ref(s.column.name)];
    } else if (AliasNode.is(s) && IdentifierNode.is(s.alias)) {
      if (ReferenceNode.is(s.node) && ColumnNode.is(s.node.column)) {
        return [
          sql.lit(s.alias.name),
          sql.id(s.node.table.table.identifier.name, s.node.column.column.name),
        ];
      } else if (ColumnNode.is(s.node)) {
        return [sql.lit(s.alias.name), sql.ref(s.node.column.name)];
      } else {
        return [sql.lit(s.alias.name), sql.ref(s.alias.name)];
      }
    } else {
      throw new Error(
        "MySQL jsonArrayFrom and jsonObjectFrom functions can only handle explicit selections due to limitations of the json_object function. selectAll() is not allowed in the subquery."
      );
    }
  });
}

function jsonArrayFrom<O>(
  expr: SelectQueryBuilder<any, any, O>
): SelectQueryBuilder<any, any, O> {
  const selection = sql`coalesce(json_arrayagg(json_object(${sql.join(
    getJsonObjectArgs(expr.toOperationNode())
  )})), '[]') `;
  return expr.clearSelect().select(selection);
}

function getJsonObjectArgs(node: SelectQueryNode): RawBuilder<unknown>[] {
  return node.selections!.flatMap(({ selection: s }) => {
    if (ReferenceNode.is(s) && ColumnNode.is(s.column)) {
      return [
        sql.lit(s.column.column.name),
        sql.id(s.table.table.identifier.name, s.column.column.name),
      ];
    } else if (ColumnNode.is(s)) {
      return [sql.lit(s.column.name), sql.ref(s.column.name)];
    } else if (AliasNode.is(s) && IdentifierNode.is(s.alias)) {
      if (ReferenceNode.is(s.node) && ColumnNode.is(s.node.column)) {
        return [
          sql.lit(s.alias.name),
          sql.id(s.node.table.table.identifier.name, s.node.column.column.name),
        ];
      } else if (ColumnNode.is(s.node)) {
        return [sql.lit(s.alias.name), sql.ref(s.node.column.name)];
      } else {
        return [sql.lit(s.alias.name), sql.ref(s.alias.name)];
      }
    } else {
      throw new Error(
        "MySQL jsonArrayFrom and jsonObjectFrom functions can only handle explicit selections due to limitations of the json_object function. selectAll() is not allowed in the subquery."
      );
    }
  });
}

SELECT
    p.id,
    (
        SELECT
            COALESCE(
                json_arrayagg(
                    json_object ('pet_id', t.id, 'name', t.name)
                ),
                '[]'
            )
        FROM
            pet t
        WHERE
            t.owner_id = p.id
    ) AS pets
FROM
    person p

SELECT
    p.id,
    (
        SELECT
            COALESCE(
                json_arrayagg(
                    json_object ('pet_id', t.id, 'name', t.name)
                ),
                '[]'
            )
        FROM
            pet t
        WHERE
            t.owner_id = p.id
    ) AS pets
FROM
    person p