Sequelize: delete JSONB property (with PostgreSQL)

Question

I'm using sequelize (v3.12.2), with pg (4.4.3), PostgreSQL (v9.4), and Node (v4.1.2).

I have a model that inlcudes a JSONB data type field.

var User = {
  data: {
    type: Sequelize.JSONB
}

Now I can do

User.findOne({where: {id: 12345}})
  .update({data: {x: 'foo'}});

And

User.findOne({where: {id: 12345}})
  .update({'data.y': 'bar'});

Now, if I want to delete just the data.x property how can I do it in one command?

User.findOne({where: {id: 12345}})
  .update({'data.x': null});

Obviously doesn't work. In fact, the resulting data object should be:

{y: 'bar'}

And not:

{x: null, y: 'bar'}

How can I do this with Sequelize? Thanks for your help!

I think it's easier to restore the whole json object in "jsonb" field.. (but maybe it's not so effecient) — Leonid Dashko
– Leonid Dashko, Commented Mar 25, 2018 at 8:33

John · Accepted Answer · 2019-09-15 08:24:36Z

2

+50

If @Tarun Lalwani's code does not work, probably Your postgresql 9.4 does not support "-" operation.

In that case, creating function or upgrade to 9.5+ would be your option.

See PostgreSQL: Remove attribute from JSON column

edited Sep 15, 2019 at 8:24

answered Sep 10, 2019 at 17:36

John

3,5951 gold badge21 silver badges29 bronze badges

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

3 Comments

John Over a year ago

@Shamoon, have you read this?stackoverflow.com/questions/23490965/…

Shamoon Over a year ago

can you please add the relevant information to your answer so this post is complete?

John Over a year ago

JSONB type is postgresql data type. Sequence is ORM mapper to DB. If postgresql (9.4) does not support certain operation like "-" for JSONB, you can't use those operations in ORM mapper neither. But from v9.5 support the operation and in Sequence "-" operation work.

Tarun Lalwani · Accepted Answer · 2019-09-08 05:16:00Z

1

I think below should work

User.findOne({where: {id: 12345}})
  .update({'data': sequelize.literal("data - 'x' ")});

Have not tested it, but if it doesn't work let me know. The solution will be along these lines only

edited Sep 8, 2019 at 5:16

answered Sep 7, 2019 at 16:27

Tarun Lalwani

147k11 gold badges216 silver badges277 bronze badges

Comments

piyushkantm · Accepted Answer · 2019-09-12 11:48:27Z

0

Try this:

User.update({
        "data": sequelize.literal("data - 'x' ") 
    }, {
        where: {
            id: 12345
        }
    });

answered Sep 12, 2019 at 11:48

piyushkantm

6747 silver badges19 bronze badges

3 Comments

Tarun Lalwani Over a year ago

How is this different from existing answer?

piyushkantm Over a year ago

It updates the database in a single Query. While yours does 2 queries. 1 to fetch the data and second to update.

GavinBelson Over a year ago

This is more practical because it can bulk update any number of records with one query

Tom McLellan · Accepted Answer · 2022-06-02 20:30:08Z

0

With Sequelize 6 (and maybe earlier), you can do the following on your model instance:

 delete myRecord.jsonBField.keyName;

Then call changed (required) to mark the field as changed and save:

 myRecord.changed("jsonBField", true);
 myRecord.save();`

Alternatively, you can call myRecord.set("jsonBField.keyName", null) to set the key to null, which will automatically mark the field as changed.

answered Jun 2, 2022 at 20:30

Tom McLellan

1765 bronze badges

Collectives™ on Stack Overflow

Sequelize: delete JSONB property (with PostgreSQL)

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related