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.)?

agershun · Accepted Answer · 2014-12-17 16:53:56Z

5

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.

answered Dec 17, 2014 at 16:53

agershun

4,10742 silver badges42 bronze badges

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

Comments

Fraction · Accepted Answer · 2019-06-21 14:54:50Z

1

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.

edited Jun 21, 2019 at 14:54

Fraction

13.1k5 gold badges32 silver badges54 bronze badges

answered Aug 7, 2013 at 0:02

The Mahahaj

6966 silver badges8 bronze badges

Collectives™ on Stack Overflow

Complex SQL query on javascript object

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related