Insert and get JSON DATA IN Sequelize and postgres

Here is an example:

import { sequelize } from '../../db';
import Sequelize, { Model } from 'sequelize';

class SomeModel extends Model {}
SomeModel.init(
  {
    data: {
      type: Sequelize.JSONB,
    },
  },
  { sequelize, modelName: 'somemodels' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // insert
    const data = { name: 'orm', age: 23 };
    await SomeModel.create({ data });

    // find
    const row = await SomeModel.findByPk(1, { raw: true });
    console.log(row);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

The execution result:

Executing (default): DROP TABLE IF EXISTS "somemodels" CASCADE;
Executing (default): DROP TABLE IF EXISTS "somemodels" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "somemodels" ("id"   SERIAL , "data" JSONB, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'somemodels' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "somemodels" ("id","data") VALUES (DEFAULT,$1) RETURNING *;
Executing (default): SELECT "id", "data" FROM "somemodels" AS "somemodels" WHERE "somemodels"."id" = 1;
{ id: 1, data: { age: 23, name: 'orm' } }

data records in the database:

=# select * from "somemodels";
 id |            data
----+----------------------------
  1 | {"age": 23, "name": "orm"}
(1 row)

I have implemented sequelize + postgres jsonb column data manipulation successfully in my project. I have implemented insertOrUpdate the jsonb column as per data availability and return updated instance. I have also shown here How to get JSONB column as well. I have also explain the code. Please take look after code.

 let options = {
        searchPath: biz,
        where: {
            id: id
        }
    };

    let staff = await db.User.findOne(options);

    if (!staff) {
        return res.send({
            error: true,
            message: 'No record found',
            status: 500
        });
    }

    // check does user.contactView (JSONB column) have object attribute or not
    // If attribute is set then update the record otherwise create new object attribute and set the value to it.
    let contactView = staff.get('contactView');

    let updatedStaff = null;
    if (_.has(contactView, 'companyViewProperties')) { // lodash.js _.has() function

        let updateOptions = {
            searchPath: biz //PostgresOnly option...
        };

        updatedStaff = await staff.update({
            'contactView.companyViewProperties': [115,125,135]
        }, updateOptions);
    } else {
        let createOptions = {
            searchPath: biz
        };

        staff.set({'contactView.companyViewProperties': [15,25,35]});
        updatedStaff = await staff.save(createOptions);
    }

return res.jsonp({
    success: true,
    message: "Record is updated successfully",
    contactView: contactView,
    user: updatedStaff
});

Explanation of code:

Note: 1) "contactView" is the jsonb column in "User" table. 2) data would be in contactView column : { companyViewProperties: [10,20,30], contactViewProperties: [1,2,3] }

Expected Output: if contactView.companyViewProperties is already set then update the value else create new contactView.companyViewProperties attribute and assign the value to it. Here is the sample code