Changeset: 7d756c61d804 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7d756c61d804
Added Files:
sql/test/json/Tests/All
sql/test/json/Tests/books.sql
Modified Files:
monetdb5/modules/atoms/json_atom.c
sql/scripts/40_json.sql
Branch: default
Log Message:
A few more SQL/JSON experiments.
diffs (96 lines):
diff --git a/monetdb5/modules/atoms/json_atom.c
b/monetdb5/modules/atoms/json_atom.c
--- a/monetdb5/modules/atoms/json_atom.c
+++ b/monetdb5/modules/atoms/json_atom.c
@@ -1541,7 +1541,7 @@ str JSONpath( json *ret, json *js, str *
switch( terms[t].token){
case INDEX:
- copying = terms[t].name && ((l > 0 &&
strncmp(terms[t].name, namebegin,l) == 0) || terms[t].name[0]=='*');
+ copying = (terms[t].name && l > 0 &&
strncmp(terms[t].name, namebegin,l) == 0) || terms[t].name == 0 ||
terms[t].name[0]=='*';
if ( copying){
if (idx == INT_MIN)
idx = terms[t].index;
diff --git a/sql/scripts/40_json.sql b/sql/scripts/40_json.sql
--- a/sql/scripts/40_json.sql
+++ b/sql/scripts/40_json.sql
@@ -27,6 +27,10 @@ returns json external name json.filter;
create function json_filter_all(js json, name string)
returns json external name json.filterall;
+-- a simple path extractor
+create function json_path(js json, e string)
+returns json external name json.path;
+
-- test string for JSON compliancy
create function json_isvalid(js string)
returns bool external name json.isvalid;
diff --git a/sql/test/json/Tests/All b/sql/test/json/Tests/All
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/All
@@ -0,0 +1,2 @@
+parsing
+books
diff --git a/sql/test/json/Tests/books.sql b/sql/test/json/Tests/books.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/books.sql
@@ -0,0 +1,58 @@
+-- based on http://goessner.net/articles/JsonPath/
+create table books(i integer, j json);
+
+insert into books values( 1, ' { "store": {
+ "book": [
+ { "category": "reference",
+ "author": "Nigel Rees",
+ "title": "Sayings of the Century",
+ "price": 8.95
+ },
+ { "category": "fiction",
+ "author": "Evelyn Waugh",
+ "title": "Sword of Honour",
+ "price": 12.99
+ },
+ { "category": "fiction",
+ "author": "Herman Melville",
+ "title": "Moby Dick",
+ "isbn": "0-553-21311-3",
+ "price": 8.99
+ },
+ { "category": "fiction",
+ "author": "J. R. R. Tolkien",
+ "title": "The Lord of the Rings",
+ "isbn": "0-395-19395-8",
+ "price": 22.99
+ }
+ ],
+ "bicycle": {
+ "color": "red",
+ "price": 19.95
+ }
+ }
+}');
+
+select * from books;
+
+-- Queries to be compiled into SQL/JSON
+-- all authors of single book in the single store
+SELECT json_filter_all(j,'author') FROM (
+ SELECT json_filter(j,'book') AS j FROM (
+ SELECT json_filter(j,'store') AS j FROM books
+ ) AS L1
+) AS L2;
+
+-- a single author from the book store
+SELECT json_filter(j,'author') FROM (
+ SELECT json_filter(j,'book') AS j FROM (
+ SELECT json_filter(j,'store') AS j FROM books
+ ) AS L1
+) AS L2;
+
+SELECT json_path(j,'..author') FROM books;
+SELECT json_path(j,'store.book[*].author') FROM books;
+SELECT json_path(j,'..bicycle.price') FROM books;
+SELECT json_path(j,'store.book.author[1]') FROM books;
+
+drop table books;
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
