Changeset: 9603470ae6ad for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9603470ae6ad
Added Files:
sql/test/json/Tests/pgexample.sql
Modified Files:
monetdb5/modules/atoms/json_atom.c
monetdb5/modules/atoms/json_atom.h
monetdb5/modules/atoms/json_atom.mal
sql/scripts/40_json.sql
sql/test/json/Tests/All
sql/test/json/Tests/books.stable.out
Branch: default
Log Message:
Add plain text generation.
diffs (206 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
@@ -1546,8 +1546,42 @@ JSONpathValidation(char *expr, pathterm
return MAL_SUCCEED;
}
-str
-JSONpath(json *ret, json *js, str *expr)
+static void
+JSONtextInternal(char *result, char *valuebegin, char *valueend)
+{
+ char *s;
+ for ( s= valuebegin; *s && s != valueend; s++){
+ if ( *s =='[' || *s ==']' || *s == '{' || *s == '}' || *s ==
',')
+ continue;
+ if ( *s == '"'){
+ *result++ = ' ';
+ for(s++; *s && *s != '"'; s++)
+ if ( *s == '\\')
+ switch (*++s){
+ case '\\':
+ *result++ = '\\';break;
+ case 'r':
+ *result++ = '\r';break;
+ case 'f':
+ *result++ = '\f';break;
+ case 't':
+ *result++ = '\t';break;
+ case 'n':
+ *result++ = '\n';break;
+ default:
+ *result++ = *s;
+ }
+ else
+ *result++ = *s;
+ *result++ = ' ';
+ } else
+ *result++ = *s;
+ }
+ *result = 0;
+}
+
+static str
+JSONpathInternal(json *ret, json *js, str *expr,int flag)
{
pathterm terms[MAXTERMS];
str j, msg = MAL_SUCCEED;
@@ -1567,9 +1601,14 @@ JSONpath(json *ret, json *js, str *expr)
result = (char *) GDKmalloc(BUFSIZ);
if (result == NULL)
throw(MAL, "json.names", MAL_MALLOC_FAIL);
- result[0] = '[';
- result[1] = 0;
- len = 1;
+ if ( !flag){
+ result[0] = '[';
+ result[1] = 0;
+ len = 1;
+ } else {
+ result[0] = 0;
+ len = 0;
+ }
lim = BUFSIZ;
idx = INT_MIN;
@@ -1639,19 +1678,30 @@ JSONpath(json *ret, json *js, str *expr)
l = valueend - valuebegin;
if (len + l + 3 > lim)
result = GDKrealloc(result, lim
+= BUFSIZ);
- strncpy(result + len, valuebegin,
valueend - valuebegin);
- len += l;
- strncpy(result + len, ",", 2);
- len++;
+ if ( !flag ){
+ strncpy(result + len,
valuebegin, valueend - valuebegin);
+ len += l;
+ strncpy(result + len, ",", 2);
+ len++;
+ } else {
+ JSONtextInternal(result + len,
valuebegin, valueend);
+ len += strlen(result+len);
+ }
}
skipblancs;
}
}
- if (result[1])
- result[len - 1] = ']';
- else {
- result[1] = ']';
- result[2] = 0;
+ if (result[1]){
+ if ( !flag )
+ result[len - 1] = ']';
+ else result[len] = 0;
+ }else {
+ if ( flag )
+ result[0] = 0;
+ else {
+ result[1] = ']';
+ result[2] = 0;
+ }
}
if (old)
GDKfree(old);
@@ -1665,3 +1715,13 @@ JSONpath(json *ret, json *js, str *expr)
*ret = result;
return msg;
}
+str
+JSONpath(json *ret, json *js, str *expr)
+{
+ return JSONpathInternal(ret,js,expr,FALSE);
+}
+str
+JSONtext(json *ret, json *js, str *expr)
+{
+ return JSONpathInternal(ret,js,expr,TRUE);
+}
diff --git a/monetdb5/modules/atoms/json_atom.h
b/monetdb5/modules/atoms/json_atom.h
--- a/monetdb5/modules/atoms/json_atom.h
+++ b/monetdb5/modules/atoms/json_atom.h
@@ -66,4 +66,5 @@ json_export str JSONprelude(int *ret);
json_export str JSONrenderobject(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
json_export str JSONrenderarray(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
json_export str JSONpath( json *ret, json *js, str *expr);
+json_export str JSONtext( json *ret, json *js, str *expr);
#endif /* JSON_H */
diff --git a/monetdb5/modules/atoms/json_atom.mal
b/monetdb5/modules/atoms/json_atom.mal
--- a/monetdb5/modules/atoms/json_atom.mal
+++ b/monetdb5/modules/atoms/json_atom.mal
@@ -119,4 +119,9 @@ address JSONrenderarray;
command path(js:json,e:str):json
address JSONpath
comment "Simple JSON path accessor";
+
+command text(js:json,e:str):str
+address JSONtext
+comment "Simple JSON path accessor, returning the concatenated string";
+
json.prelude();
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
@@ -31,6 +31,10 @@ returns json external name json.filteral
create function sys.json_path(js json, e string)
returns json external name json.path;
+-- a simple path extractor as plain text
+create function sys.json_text(js json, e string)
+returns string external name json.text;
+
-- test string for JSON compliancy
create function sys.json_isvalid(js string)
returns bool external name json.isvalid;
diff --git a/sql/test/json/Tests/All b/sql/test/json/Tests/All
--- a/sql/test/json/Tests/All
+++ b/sql/test/json/Tests/All
@@ -1,2 +1,3 @@
parsing
books
+pgexample
diff --git a/sql/test/json/Tests/books.stable.out
b/sql/test/json/Tests/books.stable.out
--- a/sql/test/json/Tests/books.stable.out
+++ b/sql/test/json/Tests/books.stable.out
@@ -88,8 +88,8 @@ Ready.
% sys.L # table_name
% json_path_j # name
% json # type
-% 12 # length
-[ "[19.95\n ]" ]
+% 7 # length
+[ "[19.95]" ]
#SELECT json_path(j,'store.book.author[1]') FROM books;
% sys.L # table_name
% json_path_j # name
diff --git a/sql/test/json/Tests/pgexample.sql
b/sql/test/json/Tests/pgexample.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/pgexample.sql
@@ -0,0 +1,24 @@
+--
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
+CREATE TABLE jsonTbl (a int, b json);
+
+INSERT INTO jsonTbl VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m
\\"Daisy\\""}');
+INSERT INTO jsonTbl VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
+INSERT INTO jsonTbl VALUES (3, '{"f1":[1,"Robert \\"M\\"",true],"f2":[2,"Kevin
\\"K\\"",false]}');
+
+--The first operator is “->”, that can be used to fetch field values directly
from JSON data. It can be used with a text value identifying the key of field.
+-- SELECT b->'f1' AS f1, b->'f3' AS f3 FROM jsonTbl WHERE a = 1;
+SELECT json_path(b,'f1') AS f1, json_path(b,'f3') FROM jsonTbl WHERE a =1;
+
+--Multiple keys can also be used in chain to retrieve data or another JSON
subset of data.
+-- SELECT b->'f1'->'f12' AS f12 FROM jsonTbl WHERE a = 2;
+ SELECT json_path(b,'f1.f12') AS f12 FROM jsonTbl WHERE a = 2;
+
+--When an integer is used as key, you can fetch data directly in a stored
array, like that for example:
+-- SELECT b->'f1'->0 as f1_0 FROM jsonTbl WHERE a = 3;
+SELECT json_path(b,'f1[0]') as f1_0 FROM jsonTbl WHERE a = 3;
+
+--The second operator added is “->>”. Contrary to “->” that returns a JSON
legal text, “->>” returns plain text.
+SELECT json_path(b,'f3') AS f1, json_text(b,'f3') FROM jsonTbl WHERE a =1;
+SELECT json_path(b,'f1[0]') as f1_0 , json_text(b,'f1[0]')FROM jsonTbl WHERE a
= 3;
+
+drop table jsonTbl;
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
