How to create a UNIQUE constraint on a JSONB field with Sequelize

Question

I'm using the Sequelize ORM in NodeJS to manage a postgreSQL database. I'm using the JSONB datatype in my table, I need an index on the JSONB field and an unique constraint on a property of this JSON. If I have to do in a classic SQL here my script :

CREATE TABLE tableJson (id SERIAL PRIMARY KEY,
                        content JSONB NOT NULL);
CREATE INDEX j_idx ON tableJson USING gin(content jsonb_path_ops);
CREATE UNIQUE INDEX content_name_idx ON tableJson(((content->>'name')::varchar));

I've found how to create the table with the INDEX but not how to deal with the UNIQUE constraint. Here is a sample of my script :

var tableJson = sequelize.define('tableJson', {
    content: Sequelize.JSONB
}, {
    indexes: [{
        fields: ['content'],
        using: 'gin',
        operator: 'jsonb_path_ops'
    }
});

Is there a solution for my problem? If not I'll probably use the sequelize.query method to execute raw query but this is not very evolutive.

Any help would be appreciated!

Masklinn · Accepted Answer · 2016-12-07 17:23:43Z

0

This is the workaround I use to add indexes on JSONB fields with Postgres and Sequelize.

First, set the quoteIdentifiers option to false in your Sequelize client.

const seq = new Sequelize(db, user, pass, {
    host: host,
    dialect: 'postgres',
    quoteIdentifiers: false,
});

(Watch out though, this will make all your table names case insensitive when created by Sequelize.sync())

You can now add indexes on JSONB fields this way in your model definition :

indexes: [
    {
        fields: ['content'],
        using: 'gin',
        operator: 'jsonb_path_ops'
    },
    {
        fields: ['((content->>\'name\')::varchar)'],
        unique: true,
        name: 'content_name_idx',
    }
],

answered Dec 7, 2016 at 17:23

Masklinn

642 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hassan Rehan · Accepted Answer · 2023-07-27 20:57:30Z

0

You have to do two things.

Set quoteIdentifiers to false.
set unique to true in indexes.

Here is the complete code

const seq = new Sequelize(db, user, pass, {
host: host,
  dialect: 'postgres',
  quoteIdentifiers: false,
});

const User = seq.define('User', {
  name: {
    type: Sequelize.STRING,
  },
  content: {
    type: Sequelize.JSONB,
  },
}, {
  indexes: [
    {
      fields: ['content'],
      using: 'gin',
      operator: 'jsonb_path_ops',
    },
    {
      fields: ['((content->>"name")::varchar)'],
      unique: true,
      name: 'content_name_idx',
    },
  ],
});

answered Jul 27, 2023 at 20:57

Hassan Rehan

1441 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to create a UNIQUE constraint on a JSONB field with Sequelize

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related