Changeset: 12498434feec for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=12498434feec
Added Files:
sql/test/json/Tests/bulkjson.sql
sql/test/json/Tests/bulkjson.stable.err
sql/test/json/Tests/bulkjson.stable.out
Modified Files:
monetdb5/modules/atoms/Tests/All
monetdb5/modules/atoms/Tests/json08.mal
monetdb5/modules/atoms/Tests/json08.stable.out
monetdb5/modules/atoms/json_atom.mal
sql/scripts/40_json.sql
Branch: default
Log Message:
Bulk operations over json tables
diffs (truncated from 358 to 300 lines):
diff --git a/monetdb5/modules/atoms/Tests/All b/monetdb5/modules/atoms/Tests/All
--- a/monetdb5/modules/atoms/Tests/All
+++ b/monetdb5/modules/atoms/Tests/All
@@ -23,6 +23,7 @@ json04
json05
json07
json08
+json09
jsonrender
uuid00
diff --git a/monetdb5/modules/atoms/Tests/json08.mal
b/monetdb5/modules/atoms/Tests/json08.mal
--- a/monetdb5/modules/atoms/Tests/json08.mal
+++ b/monetdb5/modules/atoms/Tests/json08.mal
@@ -10,8 +10,6 @@ b:= json.new("{\"f1\":1,\"f2\":2,\"f1\":
bat.append(j,b);
b:= json.new("{\"f1\":{\"f12\":3},\"f2\":[2,3,4]}");
bat.append(j,b);
-b:= json.new("[1,\"f2\", 2]");
-bat.append(j,b);
f:bat[:oid,:json]:= mal.manifold("json","filter",j,"f1");
io.print(f);
diff --git a/monetdb5/modules/atoms/Tests/json08.stable.out
b/monetdb5/modules/atoms/Tests/json08.stable.out
--- a/monetdb5/modules/atoms/Tests/json08.stable.out
+++ b/monetdb5/modules/atoms/Tests/json08.stable.out
@@ -31,8 +31,6 @@ function user.main():void;
bat.append(j,b);
b := json.new("{\"f1\":{\"f12\":3},\"f2\":[2,3,4]}");
bat.append(j,b);
- b := json.new("[1,\"f2\", 2]");
- bat.append(j,b);
f:bat[:oid,:json] := mal.manifold("json","filter",j,"f1");
io.print(f);
f:bat[:oid,:json] := mal.manifold("json","filter",j,"f1");
@@ -63,61 +61,55 @@ end main;
# void json # type
#--------------------------#
[ 0@0, "[]" ]
-[ 1@0, "[]" ]
+[ 1@0, "[1]" ]
[ 2@0, "[1]" ]
[ 3@0, "[1]" ]
-[ 4@0, "[1]" ]
-[ 5@0, "[{\"f12\":3}]" ]
+[ 4@0, "[{\"f12\":3}]" ]
+#--------------------------#
+# h t # name
+# void json # type
+#--------------------------#
+[ 0@0, "[]" ]
+[ 1@0, "[1]" ]
+[ 2@0, "[1]" ]
+[ 3@0, "[1]" ]
+[ 4@0, "[{\"f12\":3}]" ]
#--------------------------#
# h t # name
# void json # type
#--------------------------#
[ 0@0, "[]" ]
[ 1@0, "[]" ]
-[ 2@0, "[1]" ]
-[ 3@0, "[1]" ]
-[ 4@0, "[1]" ]
-[ 5@0, "[{\"f12\":3}]" ]
+[ 2@0, "[2]" ]
+[ 3@0, "[2]" ]
+[ 4@0, "[[2,3,4]]" ]
#--------------------------#
# h t # name
# void json # type
#--------------------------#
[ 0@0, "[]" ]
-[ 1@0, "[]" ]
-[ 2@0, "[]" ]
-[ 3@0, "[2]" ]
-[ 4@0, "[2]" ]
-[ 5@0, "[[2,3,4]]" ]
+[ 1@0, "[1]" ]
+[ 2@0, "[1]" ]
+[ 3@0, "[1]" ]
+[ 4@0, "[{\"f12\":3}]" ]
#--------------------------#
# h t # name
# void json # type
#--------------------------#
[ 0@0, "[]" ]
-[ 1@0, "[]" ]
+[ 1@0, "[1]" ]
[ 2@0, "[1]" ]
[ 3@0, "[1]" ]
-[ 4@0, "[1]" ]
-[ 5@0, "[{\"f12\":3}]" ]
+[ 4@0, "[{\"f12\":3}]" ]
#--------------------------#
# h t # name
# void json # type
#--------------------------#
[ 0@0, "[]" ]
-[ 1@0, "[]" ]
+[ 1@0, "[1]" ]
[ 2@0, "[1]" ]
[ 3@0, "[1]" ]
-[ 4@0, "[1]" ]
-[ 5@0, "[{\"f12\":3}]" ]
-#--------------------------#
-# h t # name
-# void json # type
-#--------------------------#
-[ 0@0, "[]" ]
-[ 1@0, "[]" ]
-[ 2@0, "[1]" ]
-[ 3@0, "[1]" ]
-[ 4@0, "[1]" ]
-[ 5@0, "[{\"f12\":3}]" ]
+[ 4@0, "[{\"f12\":3}]" ]
#next batch
#--------------------------#
# h t # name
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
@@ -49,10 +49,22 @@ command filterall(name:json, s:str) :jso
address JSONfilterObjectAll
comment "Filter the members of an object by name";
+command filter(name:json, i:bte) :json
+address JSONfilterArray
+comment "Extract a single element from an array";
+
+command filter(name:json, i:sht) :json
+address JSONfilterArray
+comment "Extract a single element from an array";
+
command filter(name:json, i:int) :json
address JSONfilterArray
comment "Extract a single element from an array";
+command filter(name:json, i:lng) :json
+address JSONfilterArray
+comment "Extract a single element from an array";
+
command isvalid(val:str):bit
address JSONisvalid
comment "Validate the string as a valid JSON document";
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
@@ -26,9 +26,15 @@ create type json external name json;
create function json.filter(js json, name string)
returns json external name json.filter;
+create function json.filter(js json, name tinyint)
+returns json external name json.filter;
+
create function json.filter(js json, name integer)
returns json external name json.filter;
+create function json.filter(js json, name bigint)
+returns json external name json.filter;
+
create function json.filter_all(js json, name string)
returns json external name json.filterall;
diff --git a/sql/test/json/Tests/bulkjson.sql b/sql/test/json/Tests/bulkjson.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/bulkjson.sql
@@ -0,0 +1,24 @@
+create table nosql(j json);
+insert into nosql values
+ ('{}'),
+ ('{"f1":1}'),
+ ('{"f1":1,"f2":2}'),
+ ('{"f1":1,"f2":2,"f1":3}'),
+ ('{"f1":{"f12":3},"f2":[2,3,4]}');
+select * from nosql;
+
+select json.filter(j,'f1') from nosql;
+select json.filter(j,'f2') from nosql;
+select json.filter(j,'f12') from nosql;
+
+delete from nosql;
+select * from nosql;
+insert into nosql values('[1,"f2", 2]');
+select * from nosql;
+
+select json.filter(j,0) from nosql;
+select json.filter(j,1) from nosql;
+select json.filter(j,2) from nosql;
+select json.filter(j,3) from nosql;
+
+drop table nosql;
diff --git a/sql/test/json/Tests/bulkjson.stable.err
b/sql/test/json/Tests/bulkjson.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/bulkjson.stable.err
@@ -0,0 +1,35 @@
+stderr of test 'bulkjson` in directory 'sql/test/json` itself:
+
+
+# 20:55:11 >
+# 20:55:11 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39328" "--set"
"mapi_usock=/var/tmp/mtest-8950/.s.monetdb.39328" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_json"
"--set" "mal_listing=0"
+# 20:55:11 >
+
+# builtin opt gdk_dbpath =
/export/scratch1/mk/current//Linux/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = yes
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 39328
+# cmdline opt mapi_usock = /var/tmp/mtest-8950/.s.monetdb.39328
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbpath =
/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_json
+# cmdline opt mal_listing = 0
+
+# 20:55:11 >
+# 20:55:11 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8950" "--port=39328"
+# 20:55:11 >
+
+
+# 20:55:11 >
+# 20:55:11 > "Done."
+# 20:55:11 >
+
diff --git a/sql/test/json/Tests/bulkjson.stable.out
b/sql/test/json/Tests/bulkjson.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/json/Tests/bulkjson.stable.out
@@ -0,0 +1,120 @@
+stdout of test 'bulkjson` in directory 'sql/test/json` itself:
+
+
+# 20:55:11 >
+# 20:55:11 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=39328" "--set"
"mapi_usock=/var/tmp/mtest-8950/.s.monetdb.39328" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/export/scratch1/mk/current//Linux/var/MonetDB/mTests_sql_test_json"
"--set" "mal_listing=0"
+# 20:55:11 >
+
+# MonetDB 5 server v11.18.0
+# This is an unreleased version
+# Serving database 'mTests_sql_test_json', using 8 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 15.591 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://vienna.ins.cwi.nl:39328/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-8950/.s.monetdb.39328
+# MonetDB/GIS module loaded
+# MonetDB/JAQL module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 20:55:11 >
+# 20:55:11 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8950" "--port=39328"
+# 20:55:11 >
+
+#create table nosql(j json);
+#insert into nosql values
+# ('{}'),
+# ('{"f1":1}'),
+# ('{"f1":1,"f2":2}'),
+# ('{"f1":1,"f2":2,"f1":3}'),
+# ('{"f1":{"f12":3},"f2":[2,3,4]}');
+[ 5 ]
+#select * from nosql;
+% sys.nosql # table_name
+% j # name
+% json # type
+% 29 # length
+[ "{}" ]
+[ "{\"f1\":1}" ]
+[ "{\"f1\":1,\"f2\":2}" ]
+[ "{\"f1\":1,\"f2\":2,\"f1\":3}" ]
+[ "{\"f1\":{\"f12\":3},\"f2\":[2,3,4]}" ]
+#select json.filter(j,'f1') from nosql;
+% sys.L # table_name
+% filter_j # name
+% json # type
+% 11 # length
+[ "[]" ]
+[ "[1]" ]
+[ "[1]" ]
+[ "[1]" ]
+[ "[{\"f12\":3}]" ]
+#select json.filter(j,'f2') from nosql;
+% sys.L # table_name
+% filter_j # name
+% json # type
+% 9 # length
+[ "[]" ]
+[ "[]" ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
