How to set a GIN index on the JSONB column?

How can I set a GIN index on the whole jsonb column and also on a single column?

import { sql } from "drizzle-orm";
import { index, jsonb, pgTable } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  metadata: jsonb("metadata").$type<Record<string, any>>(),
}, t => ({
  ginIndex: index("gin_idx").on(t.metadata).using(sql`gin`),
  ginIndex2: index("gin_idx_2").on(t.metadata).using(sql`GIN ((metadata->'name'))`),
}));

import { sql } from "drizzle-orm";
import { index, jsonb, pgTable } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  metadata: jsonb("metadata").$type<Record<string, any>>(),
}, t => ({
  ginIndex: index("gin_idx").on(t.metadata).using(sql`gin`),
  ginIndex2: index("gin_idx_2").on(t.metadata).using(sql`GIN ((metadata->'name'))`),
}));

Is currently giving me this only:

CREATE TABLE IF NOT EXISTS "users" (
    "metadata" jsonb
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_gin_idx" ON "users" ("metadata");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_name_gin_idx" ON "users" ("metadata");

CREATE TABLE IF NOT EXISTS "users" (
    "metadata" jsonb
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_gin_idx" ON "users" ("metadata");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_name_gin_idx" ON "users" ("metadata");

2 Replies

Mykhailo•7mo ago

Hey @Paul! You can use new index API for orm version 0.31 or higher & drizzle-kit version 0.22.0 or higher.

ginIndex1: index('gin_idx')
    .using('gin', t.metadata),
ginIndex2: index('gin_idx_2')
    .using('gin', sql`(metadata->'name')`),

ginIndex1: index('gin_idx')
    .using('gin', t.metadata),
ginIndex2: index('gin_idx_2')
    .using('gin', sql`(metadata->'name')`),

https://github.com/drizzle-team/drizzle-orm/releases/tag/0.31.0 But I am not sure about the second one

GitHub

Release 0.31.0 · drizzle-team/drizzle-orm

Breaking changes Note: [email protected] can be used with [email protected] or higher. The same applies to Drizzle Kit. If you run a Drizzle Kit command, it will check and prompt you for an upgr...

PaulOP•7mo ago

Awesome. Thanks so much @Mykhailo . I updated the syntax slightly for the second one and it works perfectly with the latest API.

ginIndex1: index('gin_idx_1').using('gin', t.metadata),
ginIndex2: index('gin_idx_2').using('gin', sql`metadata->'name'`),

ginIndex1: index('gin_idx_1').using('gin', t.metadata),
ginIndex2: index('gin_idx_2').using('gin', sql`metadata->'name'`),

Gives me

CREATE INDEX IF NOT EXISTS "gin_idx_1" ON "users" USING gin (metadata);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "gin_idx_2" ON "users" USING gin (metadata->'name');

CREATE INDEX IF NOT EXISTS "gin_idx_1" ON "users" USING gin (metadata);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "gin_idx_2" ON "users" USING gin (metadata->'name');

Gaming

Programming

How to set a GIN index on the JSONB column?