JsonSQL 可以方便的使用 sql 语句查询 json 数据。
示例：
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<script type="text/javascript" src="jquery-1.2.1.pack.js"></script>
<script type="text/javascript" src="jsonsql-0.1.js"></script>
<script type="text/javascript">
// 测试数据
var json = {
"channel": {
"title": "Trent's Blog",
"link": "http://trentrichardson.com",
"description": "practical programming",
"published": "Fri, 28 Dec 2007 04:27:29 +0000",
"language": "en",
"items": [{
"title": "Are You Using Regular Expressions Within SQL?",
"url": "http://trentrichardson.com/?p=45",
"published": "Fri, 28 Dec 2007 02:32:31 +0000",
"author": "trent",
"category": "programming",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=45"
},
"description": "Are You Using Regular Expressions Within SQL?Are You Using Regular Expressions Within SQL?Are You Using Regular Expressions Within SQL?"
}, {
"title": "Nothing Better than a Good Dump!",
"url": "http://trentrichardson.com/?p=44",
"published": "Mon, 24 Dec 2007 02:54:39 +0000",
"author": "trent",
"category": "javascript",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=44"
},
"description": "Nothing Better than a Good Dump!Nothing Better than a Good Dump!Nothing Better than a Good Dump!Nothing Better than a Good Dump!Nothing Better than a Good Dump!"
}, {
"title": "Javascript JSON Parsing with SQL",
"url": "http://trentrichardson.com/?p=42",
"published": "Fri, 21 Dec 2007 03:50:44 +0000",
"author": "trent",
"category": "javascript",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=42"
},
"description": "Javascript JSON Parsing with SQLJavascript JSON Parsing with SQLJavascript JSON Parsing with SQLJavascript JSON Parsing with SQL"
}, {
"title": "Microsoft += Microsoft–;",
"url": "http://trentrichardson.com/?p=41",
"published": "Thu, 20 Dec 2007 03:11:04 +0000",
"author": "trent",
"category": "vista",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=41"
},
"description": "Microsoft += Microsoft–;Microsoft += Microsoft–;Microsoft += Microsoft–;Microsoft += Microsoft–;"
}, {
"title": "Finally! JQuery Plotting - Flot",
"url": "http://trentrichardson.com/?p=40",
"published": "Fri, 07 Dec 2007 13:04:02 +0000",
"author": "trent",
"category": "javascript",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=40"
},
"description": "Finally! JQuery Plotting - FlotFinally! JQuery Plotting - FlotFinally! JQuery Plotting - FlotFinally! JQuery Plotting - FlotFinally! JQuery Plotting - Flot"
}, {
"title": "Submitting Forms with No Action",
"url": "http://trentrichardson.com/?p=39",
"published": "Thu, 06 Dec 2007 02:00:32 +0000",
"author": "trent",
"category": "programming",
"guid": {
"isPermaLink": false,
"url": "http://trentrichardson.com/?p=39"
},
"description": "Submitting Forms with No ActionSubmitting Forms with No ActionSubmitting Forms with No ActionSubmitting Forms with No ActionSubmitting Forms with No Action"
}]
}
}
$(function() {
// get all
console.log(jsonsql.query("select * from json.channel.items order by title desc,json",json));
// filter
// 注意：where 条件要放在括号中，即使只有一个查询条件，而且过滤方式也比较单一，只支持相等比较
console.log(jsonsql.query("select title,url,author,category from json.channel.items where (category=='javascript' || author=='trent') order by title,category asc limit 3", json));
// limit
console.log(jsonsql.query("select url from json.channel.items where (category=='javascript' && author=='trent') order by url asc limit 1,2", json));
});
</script>
</body>
</html>
源码
源码很简洁 jsonsql-0.1.js ：
/*
* JsonSQL
* By: Trent Richardson [http://trentrichardson.com]
* Version 0.1
* Last Modified: 1/1/2008
*
* Copyright 2008 Trent Richardson
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
var jsonsql = {
query: function(sql,json){
var returnfields = sql.match(/^(select)\s+([a-z0-9_\,\.\s\*]+)\s+from\s+([a-z0-9_\.]+)(?: where\s+\((.+)\))?\s*(?:order\sby\s+([a-z0-9_\,]+))?\s*(asc|desc|ascnum|descnum)?\s*(?:limit\s+([0-9_\,]+))?/i);
var ops = {
fields: returnfields[2].replace(' ','').split(','),
from: returnfields[3].replace(' ',''),
where: (returnfields[4] == undefined)? "true":returnfields[4],
orderby: (returnfields[5] == undefined)? []:returnfields[5].replace(' ','').split(','),
order: (returnfields[6] == undefined)? "asc":returnfields[6],
limit: (returnfields[7] == undefined)? []:returnfields[7].replace(' ','').split(',')
};
return this.parse(json, ops);
},
parse: function(json,ops){
var o = { fields:["*"], from:"json", where:"", orderby:[], order: "asc", limit:[] };
for(i in ops) o[i] = ops[i];
var result = [];
result = this.returnFilter(json,o);
result = this.returnOrderBy(result,o.orderby,o.order);
result = this.returnLimit(result,o.limit);
return result;
},
returnFilter: function(json,jsonsql_o){
var jsonsql_scope = eval(jsonsql_o.from);
var jsonsql_result = [];
var jsonsql_rc = 0;
if(jsonsql_o.where == "")
jsonsql_o.where = "true";
for(var jsonsql_i in jsonsql_scope){
with(jsonsql_scope[jsonsql_i]){
if(eval(jsonsql_o.where)){
jsonsql_result[jsonsql_rc++] = this.returnFields(jsonsql_scope[jsonsql_i],jsonsql_o.fields);
}
}
}
return jsonsql_result;
},
returnFields: function(scope,fields){
if(fields.length == 0)
fields = ["*"];
if(fields[0] == "*")
return scope;
var returnobj = {};
for(var i in fields)
returnobj[fields[i]] = scope[fields[i]];
return returnobj;
},
returnOrderBy: function(result,orderby,order){
if(orderby.length == 0)
return result;
result.sort(function(a,b){
switch(order.toLowerCase()){
case "desc": return (eval('a.'+ orderby[0] +' < b.'+ orderby[0]))? 1:-1;
case "asc": return (eval('a.'+ orderby[0] +' > b.'+ orderby[0]))? 1:-1;
case "descnum": return (eval('a.'+ orderby[0] +' - b.'+ orderby[0]));
case "ascnum": return (eval('b.'+ orderby[0] +' - a.'+ orderby[0]));
}
});
return result;
},
returnLimit: function(result,limit){
switch(limit.length){
case 0: return result;
case 1: return result.splice(0,limit[0]);
case 2: return result.splice(limit[0]-1,limit[1]);
}
}
};
资源
可以直接使用源码方式，demo地址：
http://files.cnblogs.com/zhangchen/JsonSQL.rar
也可以使用 npm 安装，地址：
