Aggregation query using Sequelize and SQLITE

Question

Using Sequelize and SQLite, and having a table like:

A | B
a | 1
b | 2
c | 3

I would like to create a map using a query via Sequelize, giving the following result:

{
  a: 1,
  b: 2,
  c: 3
}

How can I do that? I can do a findAll and then a docs.reduce(...), but I would like to extract that directly from SQLite if possible.

Shawn · Accepted Answer · 2019-07-31 05:19:48Z

1

If your version of sqlite supports the JSON1 extension, you can use something like

SELECT json_group_object(A, B) FROM yourtable;

to get a single row holding a single column that's a string holding a JSON object. That should be trivial to convert to a javascript map.

answered Jul 31, 2019 at 5:19

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

3 Comments

How do you do that using sequelize?

@IonicăBizău That's an ORM, right? They usually have some way to execute normal SQL statements.

Yes, I was wondering if there is a Sequelize-way to do that, but running a custom query will work too. Thanks!