drizzle-orm: [BUG]: jsonb type on postgres implement incorrectly

What version of `drizzle-orm` are you using?

0.29.0

What version of `drizzle-kit` are you using?

0.20.1

Describe the Bug

create tbl01 in the postgres db
run this code

import { sql } from "drizzle-orm"
import { jsonb, pgTable, text } from "drizzle-orm/pg-core"
import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"

const tbl01 = pgTable("tbl01", {
  id: text("id").primaryKey().notNull(),
  jsonb_col: jsonb("jsonb_col").notNull(),
})

// CREATE TABLE IF NOT EXISTS public.tbl01
// (
//     id text NOT NULL,
//     jsonb_col jsonb NOT NULL,
//     CONSTRAINT tbl01_pkey PRIMARY KEY (id)
// )

const dbClient = postgres("postgres://postgres:adm1n@winhost:5432/my-data")
const db = drizzle(dbClient, { schema: { tbl01 } })

console.log("INSERT INTO tbl01")
console.log(
  await db
    .insert(tbl01)
    .values({ id: "id01", jsonb_col: { field01: 100, field02: "string 100" } })
    .returning()
)

console.log("SELECT QUERY FROM tbl01")
console.log(
  await db
    .select({
      id: tbl01.id,
      jsonb_col: tbl01.jsonb_col,
      field01: sql`${tbl01.jsonb_col}->>'field01'`, // incorrect: return null, it should be "100"
      field02: sql`${tbl01.jsonb_col}->>'field02'`, // incorrect: return null, it should be "string 100"
    })
    .from(tbl01)
)

console.log("FIND MANY FROM tbl01")
console.log(
  await db.query.tbl01.findMany({
    columns: {
      id: true,
      jsonb_col: true,
      // how i add field01 and field02 in here ??
    },
  })
)

Expected behavior

jsonb type incorrectly save data as json string, so we cannot query data field using ->> operator in the postgres.

Environment & setup

No response

About this issue

Original URL
State: open
Created 8 months ago
Reactions: 2
Comments: 15

Most upvoted comments

I had a similar issue. To get around the issue I created a customJsonb type where JSON.stringify is skipped:

const customJsonb = <TData>(name: string) =>
	customType<{ data: TData; driverData: string }>({
		dataType() {
			return 'jsonb';
		},
		toDriver(value: TData) {
			return value;
		}
	})(name);

Drizzle throws a typescript error in the editor but it works and all the JSON functions work, ->> as well.

I did some more checking in the logs of my server and see that Drizzle sends an SQL statement with the following parameter when using the jsonb type provided by Drizzle: $10 = '"{\"foo\":\"bar\"}"'. Using the customJsonb above, this is: $10 = '{"foo": "bar"}'. I hope the Drizzle team can address this bug

cbasje on Nov 23, 2023

I’ve wrote a helper function to deal with nested Jsonb fields in a type-safe way. Maybe it can be helpful for someone.

import type { ColumnBaseConfig } from "drizzle-orm";
import type { PgColumn } from "drizzle-orm/pg-core";
import { SQL, StringChunk, and, eq, inArray, sql } from "drizzle-orm";

type NestedKeyOf<ObjectType> = {
  [Key in keyof ObjectType & (number | string)]: ObjectType[Key] extends Array<infer ArrayType>
    ? `${Key}.${number}` | `${Key}` | (ArrayType extends object ? `${Key}.${number}.${NestedKeyOf<ArrayType>}` : never)
    : ObjectType[Key] extends object
    ? `${Key}.${NestedKeyOf<ObjectType[Key]>}` | `${Key}`
    : `${Key}`;
}[keyof ObjectType & (number | string)];

type AtPath<T, Path extends string> = Path extends `${infer Key}.${infer Rest}`
  ? Key extends keyof T
    ? Rest extends NestedKeyOf<T[Key]>
      ? AtPath<T[Key], Rest>
      : never
    : Key extends `${number}`
    ? T extends Array<infer ArrayType>
      ? ArrayType extends object
        ? AtPath<ArrayType, Rest>
        : never
      : never
    : never
  : Path extends keyof T
  ? T[Path]
  : never;

function jsonbField<T extends PgColumn<ColumnBaseConfig<"json", "PgJsonb">>, P extends NestedKeyOf<T["_"]["data"]>>(column: T, path: P) {
  const pathParts = path.split(".");
  let concatenatedSql = "";

  pathParts.forEach((part, index) => {
    if (index === pathParts.length - 1) {
      concatenatedSql += ` -> '${part}'`;
      return;
    }
    concatenatedSql += `-> '${part}'`;
  });

  return new SQL<AtPath<T["_"]["data"], P>>([column, new StringChunk(concatenatedSql)]);
}

You can use it like this:

  const data = await dominioDb
    .select({
      someNestedField: jsonbField(someTable.jsonbData, "data.some.nested.field"),
    })
    .from(someTable)

All the arguments will be type safe.

(this assumes that you defined the type of the jsonb field in the schema declaration using the $type function.

DenisBessa on Dec 11, 2023

Hello @primadi,

Regarding your second question :

console.log(
  await db.query.tbl01.findMany({
    columns: {
      id: true,
      jsonb_col: true,
      // how i add field01 and field02 in here ??
    },
  })
)

You can add an extras key to the findMany object:

console.log(
  await database.query.tbl01.findMany({
    columns: {
      id        : true,
      jsonb_col : true,
    },
    extras: {
        field01: sql<number>`${table01.jsonb_col}->>'field01'`.as('field01'), // incorrect: return null, it should be "100"
    }
  })
)

Though I’m having the same issue regarding the jsonb accessing keys…

thomas-ndlss on Nov 15, 2023