Typing JSON field selects

[IlyaSemenov]

How to type JSON field sub-select? Consider the table:

export class Table extends BaseTable {
  readonly table = 'table'
  columns = this.setColumns(t => ({
    data: t.json(t =>
      t.object({
        foo: t.record(t.string(), t.object({ bar: t.string() })),
      }),
    ),
  }))
}

export type TableDataFoo = TableType<Table>['data']['foo']

and the query:

const row = await db.table.take().select({ foo: db.table.sql`data->'foo'` })
row.foo // unknown

I know I can use db.table.sql(t => t.whatever) but that involves copy/pasting the entire JSON subtree:

const row = await db.table.take().select({
  foo: db.table.sql(t =>
    // But what if this definition is like 30x times larger?
    t.json(t => t.record(t.string(), t.object({ bar: t.string() }))),
  )`data->'foo'`,
})
row.foo // Record<string, { bar: string }>

Is there some recipe for streamlining JSON subfield queries?

[IlyaSemenov]

Just an idea, what if this was possible?

const row = await db.table.take().select({
  foo: db.table.sql`data->'foo'`.castTo<TableDataFoo>(),
})

(Naming taken from Objection.js).

This could be useful for other raw SQL as well.

[IlyaSemenov]

This was implemented in a similar fashion: #118 (comment) 🎉

[romeerez]

The easies, but not type-safe, would be to select with a raw SQL and cast unknown to your type later:

const row = await db.table.take().select({
  foo: db.table.sql`data->'foo'`,
})
const data = row.foo as YourType

Postgres offers a lot of different functionality to work with JSON, and I planned to dive deeper into this sometime later and find ways to work with it with type safety. For now, let's not add functionality for unsafe type casting, it can be done with native TS as anyway.