[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: https://github.com/porsager/postgres/pull/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.

[AndriiSherman]

It was merged and fixed by patching a client you are providing to drizzle.

It's important that you do not use a driver client outside of Drizzle because we apply some mapping patches to it. In the upcoming updates, Drizzle will create its own clients and expose them. Additionally, we will have an updated mapping strategy, so we won't patch the clients you provide to Drizzle

It is available in drizzle-orm@beta and will remain in beta for some time (possibly a few days) before being released as the latest version

Before latest I will prepare a guide on how to fix an existing database and will reuse and mention some of the comments from this issue and a PR I've merged

[AndriiSherman]

Should be fixed in drizzle-orm@0.33.0 Please check release notes before updating