Complex SQL query on javascript object

Question

I have the following JS object:

var groups = [{id="4", name="abcd", id_group="1"}, {id="5", name="efgh", id_group="1"}, {id="6", name="ijkl", id_group="1"}, {id="4", name="abcd", id_group="2"}, {id="7", name="mnop", id_group="2"}] 

And I need to execute this SQL query on the above object:

 select id_group from groups where id in (4,7) group by id_group having count(distinct id) = 2 

The result should be:

 id_group="2" 

because only this group contains both identifiers that are used in the request.

I found information about SQLike and JSLINQ but I ran into problems with where and with expressions. Is it possible to execute such a query in a javascript object using SQL-JS libraries or JS / jQuery itself (write function, etc.)?

Answer 1

Alasql JavaScript JavaScript library has been specifically designed for the following tasks:

 <script src="alasql.min.js"></script> <script> var groups = [{id:4, name:"abcd", id_group:"1"}, {id:5, name:"efgh", id_group:"1"}, {id:6, name:"ijkl", id_group:"1"}, {id:4, name:"abcd", id_group:"2"}, {id:7, name:"mnop", id_group:"2"}]; var res = alasql('select id_group, count(id) as cnt from ? \ where id in (4,7) group by id_group having cnt = 2',[groups]); </script> 

You can try this example in jsFiddle.

I changed the SQL statement a bit because Alasql does not support aggregator functions (e.g. COUNT, SUM, MAX, MIN) inside the HAVING clause.

Answer 2

I don’t quite understand what you are asking, but with jQuery you can filter the groups object as follows:

 var filteredArr = $.grep(groups, function(obj, index) { return obj.id_group === "2" }); 

Hope this helps.