Order by JSONB data type with sequelize literal

Viewed 119

I'm currently using this structure to query for all the items:

const filteredItems = await allItems.findAll({
         where: conditions,
         include: associations,
         order: sortingCriteria,
         limit: limit,
         offset: offset,
      });

The sortingCriteria currently has this structure:

const sortingCriteria = [
      ["price", "ASC NULLS LAST"],
      ["created_at", "DESC NULLS LAST"],
    ]

Being price and created_at fields of the allItems table. In the same table I have another field called details that has a JSONB per item.

And I need to add an extra sorting criteria, so I order by some specific data that can be found in that JSONB, with this structure:

details = { 
            "sizes": {
               "height": 10,
               "width": 20,
            },
            "capacities": {
               "volume": 200,
               "weight": 2,
            }
          }

Let's say I want to order by volume all the items (and also by price and created_at, as I already have). How should I include that in sortingCriteria ?

The reason of using a JSONB instead of nested tables and JOINs is performace. I have already tried and, having everything in one table, for huge tables, the performance increases tremendously. I use limit and offset for pagination purposes. I'm using Sequelize version 6.6.2.

0 Answers
Related