MariaDB (v10) using jsonObjectFrom and jsonArrayFrom

[fj-onathan]

Hello, I've using Kysely in a production project and love it. I need some help to figure out what can be done on that cases using MariaDB version.

1. jsonArrayFrom

const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .orderBy('pet.name')
    ).as('pets')
  ])
  .execute()

select `id`, (
  select cast(coalesce(json_arrayagg(json_object(
    'pet_id', `agg`.`pet_id`,
    'name', `agg`.`name`
  )), '[]') as json) from (
    select `pet`.`id` as `pet_id`, `pet`.`name`
    from `pet`
    where `pet`.`owner_id` = `person`.`id`
    order by `pet`.`name`
  ) as `agg`
) as `pets`
from `person`

2. jsonObjectFrom

const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonObjectFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .where('pet.is_favorite', '=', true)
    ).as('favorite_pet')
  ])
  .execute()

select `id`, (
  select json_object(
    'pet_id', `obj`.`pet_id`,
    'name', `obj`.`name`
  ) from (
    select `pet`.`id` as `pet_id`, `pet`.`name`
    from `pet`
    where `pet`.`owner_id` = `person`.`id`
    and `pet`.`is_favorite` = ?
  ) as obj
) as `favorite_pet`
from `person`

---

Question.

Any possibility to do select without from internal select of, both for Array and Object? Like:

select
    id, 
    (SELECT json_object(
            'pet_id', `obj`.`pet_id`,
            'name', `obj`.`name`
            ) # Without alias there.
            from `pet`
            where `pet`.`owner_id` = `person`.`id`
            and `pet`.`is_favorite` = ?
    ) as `favorite_pet`
from `person`

[koskimas]

Copy paste the helper and make the needed modifications.

[ctiospl]

Hi, This is my edited version of the JSON helper functions for MariaDB. https://kyse.link/D5vA0 Types might be a bit off... but it works.... Cheers