[BUG]: jsonb type on postgres implement incorrectly

[primadi]

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

1. create tbl01 in the postgres db
2. 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

[primadi]

is this accepted bug, or i implemented incorrectly ?

i already try to create customType jsonb, but still cannot create jsonb data that i can access with ->> operator in postgres.

[thomas-ndlss]

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...

[Angelelz]

I attempted this directly in Postgres and I couldn't get I to work without drizzle involved.
Can you provide the SQL commands that you're trying to use? I'm just not super familiar with jsonb, and I'm trying to investigate this.

[primadi]

Hi @Angelelz

select id, jsonb_col, jsonb_col->>'field01' as field01 from tbl01

it incorrectly return null on field01, it should be 100 with above data..

but if we remove double apostrophe (") in front and end of jsonb_col value using pgAdmin, it will return result correctly.

Thank you.

[primadi]

@thomas-ndlss it works for second question. thanks.

[Angelelz]

I just ran the following query in both a local PG database and in supabase console and all I get is null. Please correct any mistake I might have:

CREATE TABLE IF NOT EXISTS public.tbl01
(
    id text NOT NULL primary key,
    jsonb_col jsonb NOT NULL
);

insert into "tbl01" ("id", "jsonb_col") values ('id01', '"{\"field01\":100,\"field02\":\"string 100\"}"') returning "id", "jsonb_col";

select id, jsonb_col, jsonb_col->>'field01' as field01, jsonb_path_exists(jsonb_col, '$.field01') from "tbl01";

Edit: My result from supabase:

id	jsonb_col	field01	jsonb_path_exists
id01	{"field01":100,"field02":"string 100"}	null	false

[primadi]

Hi @Angelelz ,

this is the correct insert :

insert into "tbl01" ("id", "jsonb_col") values ('id02', '{"field01":100,"field02":"string 100"}') returning "id", "jsonb_col";

with the correct insert, it will return the correct result for this query :

select id, jsonb_col, jsonb_col->>'field01' as field01, jsonb_path_exists(jsonb_col, '$.field01') from "tbl01";

field01: 100
jsonb_path_exists: true

[Angelelz]

So the problem is actually on insert. I thought that if you didn't have valid json, the database wouldn't let you insert it?
I just tested this and you're right. Now can you run this quick test?
Try inserting the json like this:

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

[primadi]

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

it produces data in jsonb_col:

"\"{\\\"field01\\\":100,\\\"field02\\\":\\\"string 100\\\"}\""

field01: null
jsonb_path_exists: false

[primadi]

i try:

console.log(
  await db
    .insert(tbl01)
    .values({
      id: "id02",
      jsonb_col: sql`'${JSON.stringify({
        field01: 100,
        field02: "string 100",
      })}'`,
    })
    .returning()
)

but it produce error:

PostgresError: invalid input syntax for type json
 code: "22P02"

[cbasje]

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

[primadi]

@cbasje your solution is worked, thanks. I hope Drizzle team can fixed this.

[rogiervandenberg]

There might be an error in your code @primadi I've changed the ->> to -> to get the real values. 💡 and this is my result:

[
  {
    id: 'id01',
    jsonb_col: { field01: 100, field02: 'string 100' },
    field01: 100,
    field02: 'string 100'
  }
]

with
drizzle-kit: v0.20.4
drizzle-orm: v0.29.0

Here's my full code:

import 'dotenv/config';
import { sql } from 'drizzle-orm';
import { drizzle as drizzleORM } from 'drizzle-orm/node-postgres';
import { jsonb, pgTable, text } from 'drizzle-orm/pg-core';
import { exit } from 'node:process';
import postgres from 'pg';

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

export const pool = new postgres.Pool({
	connectionString: process.env.DATABASE_URL,
});
export const db = drizzleORM(pool);

async function main() {
	console.log('Clear all');
	await db.delete(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'`, // 100
				field02: sql`${tbl01.jsonb_col}->'field02'`, //  "string 100"
			})
			.from(tbl01)
	);
}

try {
	await main();
} catch (error) {
	console.error(error.message);
	exit(1);
}
exit(0);

[cbasje]

The code by @rogiervandenberg above does work because I figured out that this problem is not present when using node-postgres. For some reason, it is only present in the connection with PostgresJS. I see from the related PR that it is an issue with PostgresJS itself: porsager/postgres#392.

So, maybe a better solution for now is to use node-postgres instead of my customType solution 😅.

[DenisBessa]

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.