[FEATURE]: Native PG jsonb query support

[jakeleventhal]

Describe what you want

If I have a column with a jsonb type, and I supply $type, there should be a native way for me to query this field without having to use the sql operator and rawdog the SQL here. The $type field gives correct typings but returned data from queries, but if I want to query into the JSON, the types should already be there and have some native support for querying this.

For instance, say I have:

myData: jsonb('myData').$type<{ a: { b: string } }>).notNull()

and I want to find a row where a.b === value. Right now, I have to do something like this:

await db.query.users.findFirst({
  where: sql`myData->'a'->>'b' = ${value}`
});

There is no type safety here. If I update, the typing of myData in the schema file, I won't get any type error in this query. I should be able to have a more ergonomic query that also provides type safety. Something like:

await db.query.users.findFirst({
  where: eq(myData.a.b, value)
});

Prisma also has a means of doing this that works well: https://www.prisma.io/docs/orm/prisma-client/special-fields-and-types/working-with-json-fields#filter-on-nested-object-property

[jakeleventhal]

Helpful resource: https://www.postgresql.org/docs/9.5/functions-json.html

[hilja]

Someone implemented a cool type-safe JSON util here #1511 (comment). My TS server ground to a halt when using it though :D But nonetheless that's a nice concept.

It makes me almost dizzy to think if jsonb_path_query would be type-safe, or if there'd be an abstraction on top of it. It's so capable.

const foo = sql`jsonb_path_query_array(
  ${reports.lighthouseResult},
  'strict $.**
    ? (exists(@.categories))
    ? (exists(@.audits)).audits.*
    ? (
        (
          (@.scoreDisplayMode == "binary" && @.score < 1)
          || ((@.scoreDisplayMode == "numeric" || @.scoreDisplayMode == "metricSavings") && @.score < 1)
        )
        && (${isPerf} || ${isDefault})
      )'
    )`

[zxt-tzx]

this worked for me, ymmv

// Type to get nested path types
type PathsToStringProperty<T> = T extends object
  ? {
      [K in keyof T & string]: T[K] extends string
        ? K
        : T[K] extends object
          ? `${K & string}.${PathsToStringProperty<T[K]>}`
          : never;
    }[keyof T & string]
  : never;

// Helper type to extract the data type from a PgColumn
type ExtractColumnData<T> =
  T extends PgColumn<infer Config, any, any>
    ? Config extends { data: any }
      ? Config["data"]
      : never
    : never;

export function jsonExtract<
  TColumn extends PgColumn<any, any, any>,
  TPath extends PathsToStringProperty<NonNullable<ExtractColumnData<TColumn>>>,
>(column: TColumn, path: TPath) {
  const parts = path.split(".");
  const lastPart = parts.pop()!;
  const pathParts = parts.length
    ? parts.map((p) => `'${p}'`).join("->") + `->'${lastPart}'`
    : `'${lastPart}'`;
  return sql`${column}->>${sql.raw(pathParts)}`;
}