Sequelize.js. How to find object in JSON datatype?

Question

I am writing query JSON data in sequelize.js using postgres db.

Postgres table structure

id: integer,
name: string,
data: json

Here is my data structure

{
    id: 3,
    name: "v4th79j"
    data: {
        phone: "123456789",
        email: "[email protected]",
        password: "$2a$10$qCttQ8leMPCzJfE",
        company_id: 2
    }
}

I am writing query like this

var filter = {};
// Filter by email for a user
filter.where = { data: { email: "[email protected]"} } ;
Entity.findOne(filter)
.then(function (entity) {
console.log(JSON.stringify(entity))
});

but it is not working. How to find object in JSON datatype?

It does not show any error, When code reach there, it stops working. — mabc224
– mabc224, Commented Dec 2, 2015 at 11:44

mabc224 · Accepted Answer · 2015-12-04 23:31:36Z

5

PostgreSQL treat json datatype as text so querying about one field is not possible. It stores the raw text of the JSON data in the database, complete with white space and retaining all the orders of any keys and any duplicate keys.

I convert data type to jsonb which is introduced in 9.4 version of PostgreSQL.

With JSONB, it turns the JSON document into a hierarchy of key/value data pairs. All the white space is discarded, only the last value in a set of duplicate keys is used and the order of keys is lost to the structure dictated by the hashes in which they are stored. We can apply index on specific field and can search any specific field using sequelize.js easily.

You can read more about with practical example here

answered Dec 4, 2015 at 23:31

mabc224

7422 gold badges10 silver badges18 bronze badges

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

1 Comment

inane Over a year ago

I am having a similar topic, I need to add to my http response one attribute with result of one specific key of my JSONB column. Do you know if it is possible?

Collectives™ on Stack Overflow

Sequelize.js. How to find object in JSON datatype?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related