Sequelize nodejs return flat JSON

Question

I am using sequelize with nodejs for a RESTful API. I have 2 models, Security and Orders. The order model references the security model using a foreign key via symbol.

Security model:

classMethods: {
      associate: function(models) {
        // associations
        security.hasMany(models.order, { foreignKey: 'security_symbol' });
      }
    }

Order model:

classMethods: {
      // associations
      associate: function(models) {
        order.belongsTo(models.security, { foreignKey: 'security_symbol' })
      }
    }

I then perform a query to return all orders which is working OK, though I want some other attributes of the security model to be included in a sort of 'flattened' json. Query:

Order.findAndCountAll({
        where: { user_id: user.id},
        attributes: [
            'id', 'side', 'quantity', 'price', 'status', 'created_at'
        ],
        order: [
            ['id', 'DESC']
        ],
        include: [{ model: models.security, attributes: ['name', 'symbol'] }]

    })

Response:

"orders": [
    {
      "id": 25,
      "side": 1,
      "quantity": 1150,
      "price": "13.33",
      "status": 0,
      "created_at": "2017-04-27T09:51:41.479Z",
      "security": {
        "name": "Car & General (K) Ltd",
        "symbol": "C&G"
      }
    },
    {
      "id": 24,
      "side": 1,
      "quantity": 1000,
      "price": "5.63",
      "status": 4,
      "created_at": "2017-04-27T09:50:31.939Z",
      "security": {
        "name": "Eveready East Africa Ltd",
        "symbol": "EVRD"
      }
    }...

Desired Response:

"orders": [
    {
      "id": 25,
      "side": 1,
      "quantity": 1150,
      "price": "13.33",
      "status": 0,
      "created_at": "2017-04-27T09:51:41.479Z",
      "security_name": "Car & General (K) Ltd",
      "security_symbol": "C&G"
    },
    {
      "id": 24,
      "side": 1,
      "quantity": 1000,
      "price": "5.63",
      "status": 4,
      "security_name": "Eveready East Africa Ltd",
      "security_symbol": "EVRD"
      "created_at": "2017-04-27T09:50:31.939Z",

    }...

Difference being the 'parent' not being nested inside the order.

Probably duplicate of http://stackoverflow.com/questions/41502699/return-flat-object-from-sequelize-with-association — Dmitry Chirkin, Apr 28 '17 at 10:11

score 3 · Answer 1 · answered May 02 '17 at 09:12

You can use the raw attribute like below

Order.findAndCountAll({
        where: { user_id: user.id},
        attributes: [
            'id', 'side', 'quantity', 'price', 'status', 'created_at'
        ],
        order: [
            ['id', 'DESC']
        ],
        include: [{ model: models.security, attributes: ['name', 'symbol'] }]
        raw: true
    })

But then sequelize will give you result like

"orders": [
    {
      "id": 25,
      "side": 1,
      "quantity": 1150,
      "price": "13.33",
      "status": 0,
      "created_at": "2017-04-27T09:51:41.479Z",
      "security.name": "Car & General (K) Ltd",
      "security.symbol": "C&G"
    },
    {
      "id": 24,
      "side": 1,
      "quantity": 1000,
      "price": "5.63",
      "status": 4,
      "security.name": "Eveready East Africa Ltd",
      "security.symbol": "EVRD"   
    }...

I'm not sure if security.name& security.symbol is desirable or not. But this is how sequelize will behave. Hope this helps

Shahar Hadas · Answer 2 · 2020-05-26T17:23:31.933

1

Try the following:

Order.findAndCountAll({
        where: { user_id: user.id},
        attributes: [
            'id', 'side', 'quantity', 'price', 'status', 'created_at',
            [Sequelize.literal('"order->security".name'), 'security_name'],
            [Sequelize.literal('"order->security".symbol'), 'security_symbol']
        ],
        order: [
            ['id', 'DESC']
        ],
        include: [{ model: models.security, attributes: []}]

    })

Please note you might need to update order->security to actual DB table names (orders and securities)

edited May 26 '20 at 17:23

answered May 10 '19 at 20:19

Shahar Hadas

2,641
1
27
29

You could also use `sequelize.col()` as found in this SO answer here: https://stackoverflow.com/a/52922156/3736404 – blaytenshi May 26 '20 at 17:18
1

agree, but `Sequelize.col()` (it's a static method) is less agnostic for name changes as you lose the ability to reference the model by the name as sequelize knows it (and that's what `literal` is designed for - to use the engine to put in the right field name) – Shahar Hadas May 26 '20 at 17:23

score 0 · Answer 3 · answered Jun 21 '22 at 17:28

If you don't want to include any additional flags and you want to flattened object in for only one caller and not in all the places you can do something like this

async function getOrders(){
Order.findAndCountAll({
        where: { user_id: user.id},
        attributes: [
            'id', 'side', 'quantity', 'price', 'status', 'created_at'
        ],
        order: [
            ['id', 'DESC']
        ],
        include: [{ model: models.security, attributes: ['name', 'symbol'] }]

    })}
const orders = await getOrders();
return orders.map(order=>order.get({raw:true}))

Sequelize nodejs return flat JSON

3 Answers3