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 above-mentioned object:

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

Result should be:

id_group="2"

because only that group contains the both ids using in query.

I found information about SQLike and JSLINQ but I have encountered problems with where in and having expressions. Is there any possibility to execute such query on javascript object using SQL-JS libraries or JS/jQuery itself (writing function etc.)?

Answer 1

Alasql JavaScript SQL library was especially designed for this type of 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 modified a little bit a SQL expression, because Alasql do not support aggregator functions (like COUNT, SUM, MAX, MIN) inside HAVING clause.

Answer 2

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

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

Hope that helped.