[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 https://github.com/drizzle-team/drizzle-orm/issues/1511#issuecomment-1850017315. 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})
      )'
    )`