My current approach involves querying for all items using the following structure:
const filteredItems = await allItems.findAll({
where: conditions,
include: associations,
order: sortingCriteria,
limit: limit,
offset: offset,
});
The sortingCriteria is structured as follows:
const sortingCriteria = [
["price", "ASC NULLS LAST"],
["created_at", "DESC NULLS LAST"],
]
The fields price and created_at belong to the allItems table. Additionally, the table contains a field named details which stores JSONB data for each item.
I now need to introduce an extra sorting criteria based on specific data within the JSONB, such as:
details = {
"sizes": {
"height": 10,
"width": 20,
},
"capacities": {
"volume": 200,
"weight": 2,
}
}
For instance, if I want to sort by
volumein addition topriceandcreated_at, how should I modify thesortingCriteria?
I opt for using JSONB instead of nested tables and JOINs for performance reasons. After experimenting, I observed significant performance improvements with all data stored in one table, particularly with large datasets. Pagination purposes are achieved through limit and offset. My Sequelize version is 6.6.2.