JSON with PostgreSQL not serializing correctly

I have define my field like this:

  hostnames: jsonb("hostnames").$type<string[]>().default([]),

  hostnames: jsonb("hostnames").$type<string[]>().default([]),

Which looks fine when I look at the table structure. Now I have the following object I'm creating/updating, which has a string[] as hostnames:

const obj = {
  name: 'foo',
  hostnames: [ 'foo.bar.com' ],
}

const obj = {
  name: 'foo',
  hostnames: [ 'foo.bar.com' ],
}

After this is added, the value in the database is escaped like this:

"[\"foo.bar.com\"]"

"[\"foo.bar.com\"]"

This does not look correct, it looks like wrongly serialized. Or what am I doing wrong here? The following SQL should extract the first value, however because it's wrongly serialized, it gives me the JSON serialization as the first value:

# select hostnames->>0 from assets limit 5;
              ?column?
════════════════════════════════════
 ["foo.bar.com"]

# select hostnames->>0 from assets limit 5;
              ?column?
════════════════════════════════════
 ["foo.bar.com"]

2 Replies

roobOP•17mo ago

All right, I can see that its a bug :/ https://github.com/drizzle-team/drizzle-orm/issues/724

GitHub

[BUG]: jsonb always inserted as a json string when using postgres-j...

What version of drizzle-orm are you using? 0.26.5 What version of drizzle-kit are you using? 0.18.1 Describe the Bug Inserting an object into a postgres jsonb field with db.insert only inserts a st...

PGT•16mo ago

thought this was fixed? guess not

Gaming

Programming

JSON with PostgreSQL not serializing correctly