Wednesday, January 16th, 2008

JsonSQL: JSON parser, SQL style

Category: JavaScript, JSON, Library

Trent Richardson has released JsonSQL, a JavaScript library that allows you to muck around with JSON as though it is a datastore:

The API

javascript

  1. jsonsql.query("select * from json.channel.items order by title desc", json);
  2.  
  3. jsonsql.query("select title,url from json.channel.items where (category=='javascript' || category=='vista') order by title,category asc limit 3", json);

Usage

  • Only Select statements are supported
  • The requested fields may be a “*” or a list of fields. “*” is likely faster in most cases.
  • When typing lists “select field1,field2,field3” or “limit 5,10 do not use spaces.
  • When using the “where” clause enclose all conditions with one set of parenthesis “where (category==’The Category’ || category==’Other Category’)”.
  • The where clause is a javascript condition, not sql. It should use the scope emplied by “from”. Javascript functions may be used here as well as javascript operators.
  • The from clause should establish the scope you would like returned. It should start with “json” and use the dot notation: “json.channel.items” and should point to an array within the object.
  • The order by option can accept a list but will only order by the first field at this time(asc,desc,ascnum,descnum).

Posted by Dion Almaer at 7:17 am
10 Comments

+++--
3.3 rating from 36 votes

10 Comments »

Comments feed TrackBack URI

Very good, impressive!

Comment by dcrec1 — January 16, 2008

Very dangerous!

Comment by JohnP — January 16, 2008

Love the idea! Seems natural to use sql for json too.

Comment by Aaron — January 16, 2008

This is a reasonable idea, and good job with implementing it, but there are some strong conceptual mismatches between SQL and JSON. JSON has an object structure, not a flat table structure. For this reason, object databases have used OQL (object query language) which looks similiar SQL, but is designed to meet the different data structure needs of object structures. However, your jsonSQL actually looks more like OQL than SQL (although of course I understand the marketability of “SQL” over “OQL”).
However, for the purpose of querying JSON object-structured data, I believe JsonPath generally provides a much cleaner, more compact, and more appropriate syntax for querying JSON data.

Comment by kriszyp — January 16, 2008

Writing it in jquery or in prototype (or in whatever great framework out there) is not so bad either:

var arr = [
{id: 1, age: 45},
{id: 2, age: 17},
{id: 3, age: 22},
{id: 4, age: 31},
{id: 5, age: 14},
{id: 6, age: 28},
{id: 7, age: 15}];

$.map(arr, function(r){ if(r.id > 4) return r.age }).sort()

Comment by sandrejev — January 16, 2008

While I think that these other approaches are generally better for JSON, I don’t want to be too negative, jsonSQL is not without merit. There may be those out there for whom SQL is their most familiar language, and I think that this might benefit them.

Comment by kriszyp — January 16, 2008

Awesome stuff. Hope author would provide DML feature as well.

Comment by kadnan — January 16, 2008

I like the idea of using a DSL for queries. For the joy of the exercise alone, here’s the equivalent in Chiron JavaScript:
include('base.js');
where(arr, get('id').then(gt(4))).each(get('age')).sorted()

I’d love to further explore translating queries in a DSL into functions that operate on JavaScript objects.

Perhaps this is a good place for partial application:
query(dsl, array of objects) -> array of objects
query(dsl) -> Query reusable compiled query
query(dsl)(array of objects) -> array of objects

Comment by KrisKowal — January 16, 2008

An interesting thing to consider is how you go “the other way”. That is, how do you produce JSON Objects that represent convenient “views” of data structured in a relational database. NextQuery is the query language of http://nextdb.net that allows you to “reshape” the results of the relational query into JSON Objects. Responding to the earlier post, that said “…there are some strong conceptual mismatches between SQL and JSON”, NextQuery addresses some of these concerns, and allows for a very natural bridge from the relational model to JSON.

Comment by nextdb — January 17, 2008

Should provide an abstraction that also caters to the upcoming Offline browser storage API.

Comment by oliverclevont — January 18, 2008

Leave a comment

You must be logged in to post a comment.