5

I am encountering a problem where in I need to manipulate a json result in a way that I could manipulate it with sql commands types.

like left joins and sum and group by

Thus anyone of you have encountered this lately?

I am currently using / exploring jsonsql javascript.. for the time beingenter image description here

attach is the json file that i need to manipuate..

the new result should be like this

enter image description here

so Im guessing I needed to use left joins in order to be result in this kind of output.

My problem is like collecting all / some parts from the rows and making it a column then putting it all together something like that???

I hope I made sense.. Thanks for all your response..

Clannad System
  • 479
  • 3
  • 9
  • 19
  • You cannot do *SQL* queries on JavaScript. JavaScript does not support SQL. Just to be pedantic. – Liam Apr 17 '14 at 16:25

1 Answers1

3

The language provides you with some nice features, so there's no need for wrapping SQL over JS:

var data=[{"firstName":"Alice", "age":"16"},{"firstName":"Bob", "age":"18"} ... {"firstName":"Zacharias", "age":"37"}]

If you want to SELECT * FROM json WHERE age>16, you could do something equivalent in JS:

data.filter(function(x){ return x.age>16 })

If you want to SELECT count(*) FROM json you just write

data.length;

If you want to SELECT avg(age) FROM json you could write

data.reduce(function(o,n){ return o+(n.age/data.length) }, 0)

If you want to SELECT sum(age) from json you could write

data.reduce(function(o,n){ return o+n.age*1 },0)

So why not using, what the language gives you?

Edit: I saw, you specified your needs. What exactly is the transformation needed? I think there should be a JS-way, to do, what you want.

Edit2: For tabular representation, you have to do a reduce over all data. For the example I simplified a little bit:

var aaData=[{"country":"USA", "month":"1", "earnings":"1000"}, {"country":"USA", "month":"2", "earnings":"1001"},     {"country":"USA", "month":"3", "earnings":"1002"}, {"country":"Germany", "month":"1", "earnings":"1000"},     {"country":"Germany", "month":"2", "earnings":"1001"}, {"country":"Germany", "month":"3", "earnings":"1002"}]

var result=aaData.reduce(function(o, n){
    if (!o.hasOwnProperty(n.country)) o[n.country]={};
    o[n.country][n.month]=n.earnings;
    return o;
}, {})

Here is a JSFiddle to play with.

Thomas Junk
  • 5,588
  • 2
  • 30
  • 43
  • Hi Thomas, Thanks, for the response but I do not have any control on the response / result from the backent that is why i need to manipulate it in json.. although i have encounter jsonsql which is pretty fine.. I believe it still lacking SUM and group by .. I think – Clannad System Apr 17 '14 at 15:58
  • When you have JSON, you have *J* ava *S* cript *O* bjects, which means, you could do, whatever JS gives you to manipulate these objects. Perhaps http://underscorejs.org/ is worth a look. – Thomas Junk Apr 17 '14 at 16:18
  • Hi Clannad. LINQ JS and TaffyDB (which are included in my answer) have sum and other aggregate functions. And considering that my answer also mention JSONSQL, i guess that she could be accepted =) – bovino Marcelo Bezerra Apr 17 '14 at 16:19
  • Hi Marcelo, thanks yes i saw it to but havent check them thoroughly because i believe they still lack the left join / sub queries. – Clannad System Apr 17 '14 at 16:24
  • @MarceloBezerra would you mind, using your own commenting section? – Thomas Junk Apr 17 '14 at 17:04
  • @ThomasJunk I do not understand exactly why you are asking this, but okay, no problem – bovino Marcelo Bezerra Apr 17 '14 at 17:09
  • I do not want to disturb your "private conversation" and do not want to get notified, when your "private conversation" updates ;) – Thomas Junk Apr 17 '14 at 17:11