JSON Array of Column Values

[prathikvel]

Hi there,

Is it possible to create a JSON array of column values in Kysely?

For example, this Kysely query...

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

...would result in nested data like this.

{
  "id": 1,
  "pets": [
    {
      "name": "Ace"
    },
    {
      "name": "Zuko"
    }
  ]
}

Is it possible to create a flat array like the following using Kysely?

{
  "id": 1,
  "pets": [
    "Ace",
    "Zuko"
  ]
}

This MySQL query would work, but I don't know how to implement it in Kysely.

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

I looked for similar issues, but I only found #819, which is loosely related. Sorry if I missed something!

[thelinuxlich]

Maybe you can create a helper similar to the one on the documentation: https://kysely.dev/docs/recipes/relations#the-json-data-type-and-functions