How can I sort by a nested JSONB attribute in sequelize

Question

I want to sort a sequelize query by a nested attribute of a JSONB object.

I have a model like that:

sequelize.define('product', {
    available: { type: Sequelize.BOOLEAN },
    price: { type: Sequelize.JSONB },
    ...
}

where price is kind of this:

price = {
    EUR: 1.2,
    CHF: 1.3,
}

I want to sort a findAll query by price.EUR. I hoped that this works:

product.findAll({ where: { available: true }, order: [['price.EUR', 'ASC']] }

But I get an unsorted array.

The underlying DB is postgres.

A solution is to sort the result programmatically in javascript (array._prototype.sort()). But I wonder if it is possible with a sequelize query.

Dmytro Mysak · Accepted Answer · 2018-11-16 05:56:36Z

2

try this:

roduct.findAll({ where: { available: true }, order: [[sequelize.literal('price->>\'EUR\''), 'ASC']] }

answered Nov 16, 2018 at 5:56

Dmytro Mysak

1,2621 gold badge15 silver badges28 bronze badges

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

1 Answer 1