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,251
1
25
25

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

Sequelize nodejs return flat JSON

2 Answers2