Kysely•2y ago

Using jsonArrayFrom with mariadb.

Hello, Madiadb does not have cast to JSON, and cannot reference nested subqueries. I'm a typescript newbie. 🙂 How can I rewrite this function to achieve a similar result with Mariadb?

11 Replies

koskimas•2y ago

Without the JSON datatype, and more importantly, the JSON aggregate functions, it might be really difficult or impossible to implement similar functionality. I'm not familiar enough with MariaDB to be able to help here

Igal•2y ago

@lemnisk8 is there some reddit/discord you could ask in? show them the compiled SQL string Kysely produces for MySQL, and ask how to achieve this with MariaDB's SQL spec. If you find out, please share the SQL here and we could move forward with typescriptying it 🙂

lemnisk8OP•2y ago

Thanks @koskimas @Igal !!! Mariadb now has support for json_arrayagg (since v10.5.0) I am trying to recreate this SQL using jsonArrayFrom

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

This is my attempt to typescripting this.

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."
      );
    }
  });
}

https://kyse.link/?p=s&i=Y46rOpewvBx9hrsblIEz Can you please check and suggest if this is the right approach and/or any corrections/pointer to doing this in a better way?

koskimas•2y ago

Well, the types are completely wrong in your code. There are also errors as you can see in the playground

lemnisk8OP•2y ago

I got it to work but I don't know how 🙂 Can you please help me fix it...? I'm still trying to figure it out.

koskimas•2y ago

This should work https://kyse.link/?p=s&i=E2pHGv3tjF7YbfOf5vyJ

lemnisk8OP•2y ago

Thanks @koskimas!

Unknown User•17mo ago

Message Not Public

koskimas•17mo ago

Debug it. See what's in the actual node and compare it to the if checks. We can't do the work for you since we don't have your code On quick glance it seems that the code expects each column reference to have a table. So this could work

const query = this.db
  .selectFrom('games as g')
  .select((eb) => [
    'g.id',
    jsonArrayFrom(
      eb
        .selectFrom('game_crypto_assets as gca')
        .select(['gca.cryptoasset_id'])
        .whereRef('gca.game_id', '=', 'g.id'),
    ).as('game_crypto_assets'),
  ])

const query = this.db
  .selectFrom('games as g')
  .select((eb) => [
    'g.id',
    jsonArrayFrom(
      eb
        .selectFrom('game_crypto_assets as gca')
        .select(['gca.cryptoasset_id'])
        .whereRef('gca.game_id', '=', 'g.id'),
    ).as('game_crypto_assets'),
  ])

Unknown User•17mo ago

Message Not Public

koskimas•17mo ago

You're welcome 😱 👌 🙃 ⭐ ✅ ❤️ 🤓 🥽 🐽 🍗 🔮

Gaming

Programming

Using jsonArrayFrom with mariadb.