Search code examples
typescriptpostgresqltypeorm

typeorm add index on jsonb column property

I have a table which has a jsonb column named asset. The record in this column has a field called value. I am looking for an option to create index of asset.value but unable to find it. Can someone help me out here? I am using typeorm.

Thanks

Solution

  • I have no experience with doing what you're asking, but I did find this article about GIN Indexes. So if you have this table

    CREATE TABLE test(id int, data JSONB, PRIMARY KEY (id));

    you can create a GIN index on it:

    CREATE INDEX datagin ON books USING gin (data);

    It doesn't look like TypeORM will support this type of specialized index at the moment. See this github issue. But you could add the CREATE INDEX and DROP INDEX manually to a TypeORM migration up() and down() function.

    Then you'd also need to run custom SQL to make use of the index with the Path Operators @>, <@:

    select * from books where data @> '{"braille":true}'::jsonb;

    Which you can do with the EntityManager API:

    const rawData = await manager.query(`select * from books where data @> '{"braille":true}'::jsonb`);