Function jsonArrayFrom

A SQLite helper for aggregating a subquery into a JSON array.

NOTE: This helper only works correctly if you've installed the ParseJSONResultsPlugin. Otherwise the nested selections will be returned as JSON strings.

The plugin can be installed like this:

import * as Sqlite from 'better-sqlite3'
import { Kysely, ParseJSONResultsPlugin, SqliteDialect } from 'kysely'
import type { Database } from 'type-editor' // imaginary module

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Sqlite(':memory:')
  }),
  plugins: [new ParseJSONResultsPlugin()]
})
Copy

Examples

import { jsonArrayFrom } from 'kysely/helpers/sqlite'

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()

result[0]?.id
result[0]?.pets[0].pet_id
result[0]?.pets[0].name
Copy

The generated SQL (SQLite):

select "id", (
  select coalesce(json_group_array(json_object(
    'pet_id', "agg"."pet_id",
    'name', "agg"."name"
  )), '[]') 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"
Copy