1

When I search the web for "Query JSON data" all I can find is a bunch of seemingly abandoned projects, dating back to 2006, 2007, 2011 for the freshest one.

My goal is to perform queries like SELECT field1, field2, fieldn FROM jsonObject WHERE... ORDER BY...

Besides a possible INSERT function would surely be a plus.

Is there a js plugin to perform such operations with javascript object (in that case I shall declare a variable = JSON.parse(jsonObject) and then work with that object?

Am I missing something native within the JSON itself? Or else, what is best practice to perform such task in 2015?

Please share...

user3553401
  • 109
  • 2
  • 15

2 Answers2

3

If you want to query JavaScript arrays in memory, you can try Alasql library. Alasql provides standard SQL interface to JavaScript objects with some special extensions, like '->' operator to point to subproperties of the object.

Here is the example of Alasql interface:

var data=[{a:{aa:1},b:{bb:2}},{a:{aa:2},b:{bb:2}},{a:{aa:1},b:{bb:3}}]
var res = alasql('SELECT SUM(a->aa), FIRST(b->bb) FROM ? \
                   GROUP BY b->bb',[data]);

You can try this example in jsFiddle.

Also, there are other libraries with SQL-like query operators and active support, including:

(Discalimer: I am the author of Alasql).

agershun
  • 4,077
  • 38
  • 41
0

If you want to do this from Oracle it's possible to do this directly in 12c. This is described in detail here:

http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies

It's also possible using 3rd party code pl_json which I've tried and seems really good http://sourceforge.net/projects/pljson/files/PL_JSON/

I believe it's possible to do the same kind of thing with the current version of postgres

kayakpim
  • 985
  • 2
  • 8
  • 28