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
Mykhailo6mo 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...
Paul
PaulOP6mo 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');
Want results from more Discord servers?
Add your server