How to access JSON nested value within a `select()`

Having this schema:

type Breakdown = {
  land: number;
  construction: number;
  other: number;
};

export const table = mysqlTable("table", {
  id: varchar("id", { length: 32 }).primaryKey(),
  ...
  debt: json("debt")
    .$type<Breakdown>()
    .default({ land: 0, construction: 0, other: 0 })
    .notNull(),

type Breakdown = {
  land: number;
  construction: number;
  other: number;
};

export const table = mysqlTable("table", {
  id: varchar("id", { length: 32 }).primaryKey(),
  ...
  debt: json("debt")
    .$type<Breakdown>()
    .default({ land: 0, construction: 0, other: 0 })
    .notNull(),

I would like to access debt values to compute the total in the select():

return db.select({
  ...getTableColumns(table),
  debt_total: table.debt.land + table.debt.construction + table.land.other,
})...

return db.select({
  ...getTableColumns(table),
  debt_total: table.debt.land + table.debt.construction + table.land.other,
})...

It does not seem possible as the moment or did I miss something?

2 Replies

Mario564•2w ago

@binajmen Drizzle doesn't have a util type to handle this case with TS functions, so you'll have to use the sql operator for this:

debt_total: sql`${table.debt} -> 'land' + ${table.debt} -> 'construction' + ${table.debt} -> 'other'`.mapWith(Number)

debt_total: sql`${table.debt} -> 'land' + ${table.debt} -> 'construction' + ${table.debt} -> 'other'`.mapWith(Number)

Mario564•2w ago

You can find more info on accessing JSON properties in PG here: https://www.postgresql.org/docs/9.3/functions-json.html

PostgreSQL Documentation

JSON Functions and Operators

Gaming

Programming

How to access JSON nested value within a `select()`