diff options
author | Olivier Bertrand <bertrandop@gmail.com> | 2015-12-04 01:02:27 +0100 |
---|---|---|
committer | Olivier Bertrand <bertrandop@gmail.com> | 2015-12-04 01:02:27 +0100 |
commit | 0ec89291f139a881471020a216b52333e965156e (patch) | |
tree | ca2ef596a8038429a8534f32a099e251c1948f16 /storage/connect/mysql-test/connect/r | |
parent | 8a860fda0c1b0c9fda2fcddc0d3392a20d76a9d8 (diff) | |
download | mariadb-git-0ec89291f139a881471020a216b52333e965156e.tar.gz |
Remove warning on Linux
modified: storage/connect/filamzip.cpp
Avoid calling the wrong AddValue (Windows compiler error ???)
modified: storage/connect/json.h
Fix looping bug in JARRAY::AddValue for arrays having one value.
Fix potential crash in JVALUE::SetValue
modified: storage/connect/json.cpp
Many changes to fix bugs, enhance memory handling and modify Jpath.
In JSNX some functions have been re-written or added to handle new Jpath.
BMX was re-defined to avoid a different size between Windows an Linux.
Jbin memory was fixed to use the proper memory when adding values.
Default pretty value is now 0 instead of 2.
Filename is stored in BSON when IsJson == 2.
BSON size is added to memlen in CalcLen when applicable.
The order or parameter was switch in Json_Object_Grp.
File name argument must be a constant (temporary?)
Json_Set_Item now returns file name when applicable.
modified: storage/connect/jsonudf.cpp
modified: storage/connect/jsonudf.h
Include "mycat.h"
modified: storage/connect/mycat.cc
Udf_json test revisited and fixed for Linux
modified: storage/connect/mysql-test/connect/r/json_udf.result
modified: storage/connect/mysql-test/connect/t/json_udf.inc
modified: storage/connect/mysql-test/connect/t/json_udf.test
modified: storage/connect/mysql-test/connect/t/json_udf2.inc
Diffstat (limited to 'storage/connect/mysql-test/connect/r')
-rw-r--r-- | storage/connect/mysql-test/connect/r/json_udf.result | 220 | ||||
-rw-r--r-- | storage/connect/mysql-test/connect/r/json_udf_bin.result | 588 |
2 files changed, 725 insertions, 83 deletions
diff --git a/storage/connect/mysql-test/connect/r/json_udf.result b/storage/connect/mysql-test/connect/r/json_udf.result index e9a1265d0fe..5089022c5ea 100644 --- a/storage/connect/mysql-test/connect/r/json_udf.result +++ b/storage/connect/mysql-test/connect/r/json_udf.result @@ -2,11 +2,14 @@ CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=VIR BLOCK_SIZE=5; # # Test UDF's with constant arguments # -SELECT JsonValue(56,3.1416,'foo',NULL); +SELECT JsonValue(56, 3.1416, 'foo', NULL); ERROR HY000: Can't initialize function 'jsonvalue'; Cannot accept more than 1 argument SELECT JsonValue(3.1416); JsonValue(3.1416) 3.141600 +SELECT JsonValue(-80); +JsonValue(-80) +-80 SELECT JsonValue('foo'); JsonValue('foo') "foo" @@ -25,8 +28,8 @@ false SELECT JsonValue(); JsonValue() null -SELECT JsonValue('[11,22,33]' json_) FROM t1; -JsonValue('[11,22,33]' json_) +SELECT JsonValue('[11, 22, 33]' json_) FROM t1; +JsonValue('[11, 22, 33]' json_) [11,22,33] [11,22,33] [11,22,33] @@ -35,27 +38,29 @@ JsonValue('[11,22,33]' json_) SELECT Json_Array(); Json_Array() [] -SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL); -Json_Array(56,3.1416,'My name is "Foo"',NULL) +SELECT Json_Array(56, 3.1416, 'My name is "Foo"', NULL); +Json_Array(56, 3.1416, 'My name is "Foo"', NULL) [56,3.141600,"My name is \"Foo\"",null] -SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL); -Json_Array(Json_Array(56,3.1416,'foo'),NULL) -[[56,3.141600,"foo"],null] -SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array; +SELECT Json_Array(Json_Array(56, 3.1416, 'foo'), TRUE); +Json_Array(Json_Array(56, 3.1416, 'foo'), TRUE) +[[56,3.141600,"foo"],true] +SELECT Json_Array_Add(Json_Array(56, 3.1416, 'foo', NULL)) Array; ERROR HY000: Can't initialize function 'json_array_add'; This function must have at least 2 arguments -SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array; +SELECT Json_Array_Add(Json_Array(56, 3.1416, 'foo', NULL), 'One more') Array; Array [56,3.141600,"foo",null,"One more"] -SELECT Json_Array_Add(JsonValue('one value'),'One more'); +SELECT Json_Array_Add(JsonValue('one value'), 'One more'); ERROR HY000: Can't initialize function 'json_array_add'; First argument must be a json item -SELECT Json_Array_Add('one value','One more'); +SELECT Json_Array_Add('one value', 'One more'); ERROR HY000: Can't initialize function 'json_array_add'; First argument must be a json item -SELECT Json_Array_Add('one value' json_,'One more'); -Json_Array_Add('one value' json_,'One more') +SELECT Json_Array_Add('one value' json_, 'One more'); +Json_Array_Add('one value' json_, 'One more') one value Warnings: Warning 1105 Error 2 opening one value Warning 1105 First argument target is not an array +SELECT Json_Array_Add(5 json_, 'One more'); +ERROR HY000: Can't initialize function 'json_array_add'; First argument must be a json item SELECT Json_Array_Add('[5,3,8,7,9]' json_, 4, 0); Json_Array_Add('[5,3,8,7,9]' json_, 4, 0) [4,5,3,8,7,9] @@ -65,6 +70,15 @@ Array SELECT Json_Array_Add('[5,3,8,7,9]' json_, 4, 9); Json_Array_Add('[5,3,8,7,9]' json_, 4, 9) [5,3,8,7,9,4] +SELECT Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), '[2]', 33, 1); +Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), '[2]', 33, 1) +[1,2,[11,22],"[2]"] +SELECT Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), 33, '[2]', 1); +Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), 33, '[2]', 1) +[1,2,[11,33,22]] +SELECT Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), 33, 1, '[2]'); +Json_Array_Add(Json_Array(1, 2, Json_Array(11, 22)), 33, 1, '[2]') +[1,2,[11,33,22]] SELECT Json_Array_Add_Values(Json_Array(56, 3.1416, 'machin', NULL), 'One more', 'Two more') Array; Array [56,3.141600,"machin",null,"One more","Two more"] @@ -92,19 +106,24 @@ Array SELECT Json_Array_Add_Values('[56]', 3.1416, 'machin') Array; Array [56,3.141600,"machin"] -SELECT Json_Array_Delete(Json_Array(56,3.1416,'My name is "Foo"',NULL),0); -Json_Array_Delete(Json_Array(56,3.1416,'My name is "Foo"',NULL),0) +SELECT Json_Array_Delete(Json_Array(56, 3.1416, 'My name is "Foo"', NULL), 0); +Json_Array_Delete(Json_Array(56, 3.1416, 'My name is "Foo"', NULL), 0) [3.141600,"My name is \"Foo\"",null] -SELECT Json_Array_Delete(Json_Object(56,3.1416,'My name is Foo',NULL),2); -Json_Array_Delete(Json_Object(56,3.1416,'My name is Foo',NULL),2) +SELECT Json_Array_Delete(Json_Object(56, 3.1416, 'My name is Foo', NULL), 2); +Json_Array_Delete(Json_Object(56, 3.1416, 'My name is Foo', NULL), 2) {"56":56,"3.1416":3.141600,"My name is Foo":"My name is Foo","NULL":null} Warnings: Warning 1105 First argument target is not an array -SELECT Json_Array_Delete(Json_Array(56,3.1416,'My name is "Foo"',NULL),'2'); -Json_Array_Delete(Json_Array(56,3.1416,'My name is "Foo"',NULL),'2') +SELECT Json_Array_Delete(Json_Array(56, 3.1416, 'My name is "Foo"', NULL), '2'); +Json_Array_Delete(Json_Array(56, 3.1416, 'My name is "Foo"', NULL), '2') [56,3.141600,"My name is \"Foo\"",null] Warnings: Warning 1105 Missing or null array index +SELECT Json_Array_Delete(json_array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2); +Json_Array_Delete(json_array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2) +[56,3.141600,"My name is \"Foo\"",null] +Warnings: +Warning 1105 No sub-item at '2' SELECT Json_Object(56, 3.1416, 'foo', NULL); Json_Object(56, 3.1416, 'foo', NULL) {"56":56,"3.1416":3.141600,"foo":"foo","NULL":null} @@ -120,19 +139,30 @@ Json_Object(Json_Array(56, 3.1416, 'foo'), NULL) SELECT Json_Array(Json_Object(56 "qty", 3.1416 "price", 'foo') ,NULL); Json_Array(Json_Object(56 "qty", 3.1416 "price", 'foo') ,NULL) [{"qty":56,"price":3.141600,"foo":"foo"},null] -SELECT Json_Object_Add(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'blue' color); -Json_Object_Add(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'blue' color) +SELECT Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL); +Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL) +{"qty":56,"price":3.141600,"truc":"machin","garanty":null} +SELECT Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty'); +ERROR HY000: Can't initialize function 'json_object_key'; This function must have an even number of arguments +SELECT Json_Object_Add(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color); +Json_Object_Add(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color) {"qty":56,"price":3.141600,"truc":"machin","garanty":null,"color":"blue"} -SELECT Json_Object_Add(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 45.99 price); -Json_Object_Add(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 45.99 price) +SELECT Json_Object_Add(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price); +Json_Object_Add(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price) {"qty":56,"price":45.990000,"truc":"machin","garanty":null} -SELECT Json_Object_Delete(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'truc'); -Json_Object_Delete(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'truc') +SELECT Json_Object_Add(Json_File('notexist.json'), 'cheese' item, '[1]', 1); +Json_Object_Add(Json_File('notexist.json'), 'cheese' item, '[1]', 1) +NULL +Warnings: +Warning 1105 Error 2 opening notexist.json +Warning 1105 First argument target is not an object +SELECT Json_Object_Delete(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc'); +Json_Object_Delete(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc') {"qty":56,"price":3.141600,"garanty":null} -SELECT Json_Object_Delete(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'chose'); -Json_Object_Delete(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty), 'chose') +SELECT Json_Object_Delete(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose'); +Json_Object_Delete(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose') {"qty":56,"price":3.141600,"truc":"machin","garanty":null} -SELECT Json_Object_List(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty)) "Key List"; +SELECT Json_Object_List(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty)) "Key List"; Key List ["qty","price","truc","garanty"] SELECT Json_Object_List('{"qty":56, "price":3.1416, "truc":"machin", "garanty":null}') "Key List"; @@ -188,6 +218,16 @@ DEPARTMENT Json_Array_Grp(NAME) Warnings: Warning 1105 Result truncated to json_grp_size values SET connect_json_grp_size=30; +SELECT Json_Object(title, Json_Array_Grp(name) `json_names`) from t3 GROUP BY title; +Json_Object(title, Json_Array_Grp(name) `json_names`) +{"title":"ADMINISTRATOR","names":["GOOSEPEN","FUNNIGUY","SHRINKY"]} +{"title":"DIRECTOR","names":["QUINN","WERTHER","STRONG"]} +{"title":"ENGINEER","names":["BROWNY","ORELLY","MARTIN","TONGHO","WALTER","SMITH"]} +{"title":"PROGRAMMER","names":["BUGHAPPY"]} +{"title":"SALESMAN","names":["WHEELFOR","MERCHANT","BULLOZER","BANCROFT","FODDERMAN"]} +{"title":"SCIENTIST","names":["BIGHEAD","BIGHORN"]} +{"title":"SECRETARY","names":["MESSIFUL","HONEY","SHORTSIGHT","CHERRY","MONAPENNY"]} +{"title":"TYPIST","names":["KITTY","PLUMHEAD"]} SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t3 GROUP BY DEPARTMENT; Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) ["0021",["STRONG","SHORTSIGHT"]] @@ -227,12 +267,12 @@ Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY {"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.000000},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.000000}]} {"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.000000}]} SELECT Json_Object_Grp(SALARY) FROM t3; -ERROR HY000: Can't initialize function 'json_object_grp'; This function requires 2 arguments (value, key) -SELECT Json_Object_Grp(SALARY, NAME) FROM t3; -Json_Object_Grp(SALARY, NAME) +ERROR HY000: Can't initialize function 'json_object_grp'; This function requires 2 arguments (key, value) +SELECT Json_Object_Grp(NAME, SALARY) FROM t3; +Json_Object_Grp(NAME, SALARY) {"BANCROFT":9600.000000,"SMITH":9000.000000,"MERCHANT":8700.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"BIGHEAD":8000.000000,"SHRINKY":7500.000000,"WALTER":7400.000000,"FODDERMAN":7000.000000,"TONGHO":6800.000000,"SHORTSIGHT":5500.000000,"MESSIFUL":5000.500000,"HONEY":4900.000000,"GOOSEPEN":4700.000000,"CHERRY":4500.000000,"MONAPENNY":3800.000000,"KITTY":3000.450000,"PLUMHEAD":2800.000000,"STRONG":23000.000000,"BULLOZER":14800.000000,"WERTHER":14500.000000,"QUINN":14000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"BROWNY":10500.000000,"WHEELFOR":10030.000000,"MARTIN":10000.000000} -SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t3 GROUP BY DEPARTMENT; -Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") +SELECT Json_Object(DEPARTMENT, Json_Object_Grp(NAME, SALARY) "Json_SALARIES") FROM t3 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Object_Grp(NAME, SALARY) "Json_SALARIES") {"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.000000,"SHORTSIGHT":5500.000000}} {"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.000000,"PLUMHEAD":2800.000000,"HONEY":4900.000000,"TONGHO":6800.000000,"WALTER":7400.000000,"SHRINKY":7500.000000,"WERTHER":14500.000000,"MERCHANT":8700.000000,"WHEELFOR":10030.000000}} {"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.000000,"QUINN":14000.000000,"BROWNY":10500.000000,"KITTY":3000.450000,"MONAPENNY":3800.000000,"MARTIN":10000.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"FODDERMAN":7000.000000,"MESSIFUL":5000.500000,"GOOSEPEN":4700.000000}} @@ -240,6 +280,20 @@ Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") SELECT Json_Array_Grp(NAME) FROM t3; Json_Array_Grp(NAME) ["BANCROFT","SMITH","MERCHANT","FUNNIGUY","BUGHAPPY","BIGHEAD","SHRINKY","WALTER","FODDERMAN","TONGHO","SHORTSIGHT","MESSIFUL","HONEY","GOOSEPEN","CHERRY","MONAPENNY","KITTY","PLUMHEAD","STRONG","BULLOZER","WERTHER","QUINN","ORELLY","BIGHORN","BROWNY","WHEELFOR","MARTIN"] +SELECT Json_Object_Key(name, title) FROM t3 WHERE DEPARTMENT = 318; +Json_Object_Key(name, title) +{"BANCROFT":"SALESMAN"} +{"MERCHANT":"SALESMAN"} +{"SHRINKY":"ADMINISTRATOR"} +{"WALTER":"ENGINEER"} +{"TONGHO":"ENGINEER"} +{"HONEY":"SECRETARY"} +{"PLUMHEAD":"TYPIST"} +{"WERTHER":"DIRECTOR"} +{"WHEELFOR":"SALESMAN"} +SELECT Json_Object_Grp(name, title) FROM t3 WHERE DEPARTMENT = 318; +Json_Object_Grp(name, title) +{"BANCROFT":"SALESMAN","MERCHANT":"SALESMAN","SHRINKY":"ADMINISTRATOR","WALTER":"ENGINEER","TONGHO":"ENGINEER","HONEY":"SECRETARY","PLUMHEAD":"TYPIST","WERTHER":"DIRECTOR","WHEELFOR":"SALESMAN"} # # Test value getting UDF's # @@ -304,40 +358,40 @@ JsonGet_Int(@j1, '[#]') SELECT JsonGet_Int(@j1, '[+]'); JsonGet_Int(@j1, '[+]') 243 -SELECT JsonGet_Int(@j1 json_,'[3]'); -JsonGet_Int(@j1 json_,'[3]') +SELECT JsonGet_Int(@j1 json_, '[3]'); +JsonGet_Int(@j1 json_, '[3]') 45 -SELECT JsonGet_Int(Json_Array(45,28,36,45,89),'[3]'); -JsonGet_Int(Json_Array(45,28,36,45,89),'[3]') +SELECT JsonGet_Int(Json_Array(45,28,36,45,89), '[3]'); +JsonGet_Int(Json_Array(45,28,36,45,89), '[3]') 45 -SELECT JsonGet_Int(Json_Array(45,28,36,45,89),'["+"]'); -JsonGet_Int(Json_Array(45,28,36,45,89),'["+"]') +SELECT JsonGet_Int(Json_Array(45,28,36,45,89), '["+"]'); +JsonGet_Int(Json_Array(45,28,36,45,89), '["+"]') 45 -SELECT JsonGet_Int(Json_Array(45,28,36,45,89),'[+]'); -JsonGet_Int(Json_Array(45,28,36,45,89),'[+]') +SELECT JsonGet_Int(Json_Array(45,28,36,45,89), '[+]'); +JsonGet_Int(Json_Array(45,28,36,45,89), '[+]') 243 -SELECT JsonGet_Int(Json_Array(json_array(45,28),json_array(36,45,89)),'[1]:[0]'); -JsonGet_Int(Json_Array(json_array(45,28),json_array(36,45,89)),'[1]:[0]') +SELECT JsonGet_Int(Json_Array(json_array(45,28), json_array(36,45,89)), '[1]:[0]'); +JsonGet_Int(Json_Array(json_array(45,28), json_array(36,45,89)), '[1]:[0]') 36 -SELECT JsonGet_Int(Json_Array(json_array(45,28),json_array(36,45,89)),'[0]:[1]'); -JsonGet_Int(Json_Array(json_array(45,28),json_array(36,45,89)),'[0]:[1]') +SELECT JsonGet_Int(Json_Array(json_array(45,28), json_array(36,45,89)), '[0]:[1]'); +JsonGet_Int(Json_Array(json_array(45,28), json_array(36,45,89)), '[0]:[1]') 28 -SELECT JsonGet_Int(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'qty'); -JsonGet_Int(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'qty') +SELECT JsonGet_Int(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty'); +JsonGet_Int(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty') 56 -SELECT JsonGet_Int(@j2 json_,'price'); -JsonGet_Int(@j2 json_,'price') +SELECT JsonGet_Int(@j2 json_, 'price'); +JsonGet_Int(@j2 json_, 'price') 3 -SELECT JsonGet_Int(@j2,'qty'); -JsonGet_Int(@j2,'qty') +SELECT JsonGet_Int(@j2, 'qty'); +JsonGet_Int(@j2, 'qty') 56 -SELECT JsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','chose'); -JsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','chose') +SELECT JsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); +JsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') NULL -SELECT JsonGet_Int(JsonGet_String(Json_Array(Json_Array(45,28),Json_Array(36,45,89)),'[1]:*'),'[+]') sum; +SELECT JsonGet_Int(JsonGet_String(Json_Array(Json_Array(45,28),Json_Array(36,45,89)), '[1]:*'), '[+]') sum; sum 170 -SELECT department, JsonGet_Int(Json_Object(department, Json_Array_Grp(salary) "Json_salaries"),'salaries:[+]') Sumsal FROM t3 GROUP BY department; +SELECT department, JsonGet_Int(Json_Object(department, Json_Array_Grp(salary) "Json_salaries"), 'salaries:[+]') Sumsal FROM t3 GROUP BY department; department Sumsal 0021 28500 0318 72230 @@ -346,38 +400,38 @@ department Sumsal SELECT JsonGet_Real(@j1, '[2]'); JsonGet_Real(@j1, '[2]') 36.000000000000000 -SELECT JsonGet_Real(@j1 json_,'[3]',2); -JsonGet_Real(@j1 json_,'[3]',2) +SELECT JsonGet_Real(@j1 json_, '[3]', 2); +JsonGet_Real(@j1 json_, '[3]', 2) 45.00 -SELECT JsonGet_Real(Json_Array(45,28,36,45,89),'[3]'); -JsonGet_Real(Json_Array(45,28,36,45,89),'[3]') +SELECT JsonGet_Real(Json_Array(45,28,36,45,89), '[3]'); +JsonGet_Real(Json_Array(45,28,36,45,89), '[3]') 45.000000000000000 -SELECT JsonGet_Real(Json_Array(45,28,36,45,89),'["+"]'); -JsonGet_Real(Json_Array(45,28,36,45,89),'["+"]') +SELECT JsonGet_Real(Json_Array(45,28,36,45,89), '["+"]'); +JsonGet_Real(Json_Array(45,28,36,45,89), '["+"]') 45.000000000000000 -SELECT JsonGet_Real(Json_Array(45,28,36,45,89),'[+]'); -JsonGet_Real(Json_Array(45,28,36,45,89),'[+]') +SELECT JsonGet_Real(Json_Array(45,28,36,45,89), '[+]'); +JsonGet_Real(Json_Array(45,28,36,45,89), '[+]') 243.000000000000000 -SELECT JsonGet_Real(Json_Array(45,28,36,45,89),'[!]'); -JsonGet_Real(Json_Array(45,28,36,45,89),'[!]') +SELECT JsonGet_Real(Json_Array(45,28,36,45,89), '[!]'); +JsonGet_Real(Json_Array(45,28,36,45,89), '[!]') 48.600000000000000 -SELECT JsonGet_Real(Json_Array(json_array(45,28),json_array(36,45,89)),'[1]:[0]'); -JsonGet_Real(Json_Array(json_array(45,28),json_array(36,45,89)),'[1]:[0]') +SELECT JsonGet_Real(Json_Array(json_array(45,28), json_array(36,45,89)), '[1]:[0]'); +JsonGet_Real(Json_Array(json_array(45,28), json_array(36,45,89)), '[1]:[0]') 36.000000000000000 -SELECT JsonGet_Real(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'price'); -JsonGet_Real(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'price') +SELECT JsonGet_Real(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price'); +JsonGet_Real(Json_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price') 3.141600000000000 -SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_,'qty'); -JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_,'qty') +SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty'); +JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty') 56.000000000000000 -SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','price'); -JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','price') +SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price'); +JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price') 3.141600000000000 -SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','price', 4); -JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','price', 4) +SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4); +JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4) 3.1416 -SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','chose'); -JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}','chose') +SELECT JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); +JsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') NULL SELECT department, JsonGet_Real(Json_Object(department, Json_Array_Grp(salary) "Json_salaries"),'salaries:[+]') Sumsal FROM t3 GROUP BY department; department Sumsal @@ -397,12 +451,12 @@ JsonGet_Real(Json_Array(45,28,36,45,89), '[!]', 2) "Avg"; Rank Number Concat Sum Avg 89 5 45,28,36,45,89 243 48.60 SELECT -JsonGet_String('{"qty":7,"price":29.50,"garanty":null}','price') "String", -JsonGet_Int('{"qty":7,"price":29.50,"garanty":null}','price') "Int", -JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}','price') "Real"; +JsonGet_String('{"qty":7,"price":29.50,"garanty":null}', 'price') "String", +JsonGet_Int('{"qty":7,"price":29.50,"garanty":null}', 'price') "Int", +JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price') "Real"; String Int Real 29.50 29 29.500000000000000 -SELECT JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}','price',3) "Real"; +SELECT JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price', 3) "Real"; Real 29.500 # @@ -511,7 +565,7 @@ Json_Locate_All('test/biblio.json' jfile_, 'Knab') # # Testing json files # -select Jfile_Make('[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]}, +SELECT Jfile_Make('[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]}, {"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]}, {"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]}, {"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}]', 'test/fx.json', 0) AS NewFile; diff --git a/storage/connect/mysql-test/connect/r/json_udf_bin.result b/storage/connect/mysql-test/connect/r/json_udf_bin.result new file mode 100644 index 00000000000..4e59b51c529 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/json_udf_bin.result @@ -0,0 +1,588 @@ +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=VIR BLOCK_SIZE=3; +# +# Test Jbin UDF's +# +SELECT Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), n) from t1; +Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), n) +[56,3.141600,"My name is \"Foo\"",null,1] +[56,3.141600,"My name is \"Foo\"",null,2] +[56,3.141600,"My name is \"Foo\"",null,3] +SELECT Json_Array_Add(Jbin_Array(n, 3.1416, 'My name is "Foo"', NULL), n) from t1; +Json_Array_Add(Jbin_Array(n, 3.1416, 'My name is "Foo"', NULL), n) +[1,3.141600,"My name is \"Foo\"",null,1] +[2,3.141600,"My name is \"Foo\"",null,2] +[3,3.141600,"My name is \"Foo\"",null,3] +SELECT Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), Jbin_Array('a','b',n)) from t1; +Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), Jbin_Array('a','b',n)) +[56,3.141600,"My name is \"Foo\"",null,["a","b",1]] +[56,3.141600,"My name is \"Foo\"",null,["a","b",2]] +[56,3.141600,"My name is \"Foo\"",null,["a","b",3]] +SELECT Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), JsonGet_String(Jbin_Array('a','b','c'), '[1]')); +Json_Array_Add(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), JsonGet_String(Jbin_Array('a','b','c'), '[1]')) +[56,3.141600,"My name is \"Foo\"",null,"b"] +SELECT Json_Array_Delete(Jbin_Array_Add_Values(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), "One more", 2), 4); +Json_Array_Delete(Jbin_Array_Add_Values(Jbin_Array(56, 3.1416, 'My name is "Foo"', NULL), "One more", 2), 4) +[56,3.141600,"My name is \"Foo\"",null,2] +SELECT Json_Array_Delete(Jbin_Array(56, Jbin_Array(3.1416, 'My name is "Foo"'), NULL), '[1]', 1); +Json_Array_Delete(Jbin_Array(56, Jbin_Array(3.1416, 'My name is "Foo"'), NULL), '[1]', 1) +[56,[3.141600],null] +SELECT Json_Array_Delete(Jbin_Array(56, Jbin_Array(3.1416, 'My name is "Foo"'), TRUE), 1, '[1]'); +Json_Array_Delete(Jbin_Array(56, Jbin_Array(3.1416, 'My name is "Foo"'), TRUE), 1, '[1]') +[56,[3.141600],true] +SELECT Json_Array(1, TRUE, 0, FALSE); +Json_Array(1, TRUE, 0, FALSE) +[1,true,0,false] +SELECT Json_Serialize(Jbin_Array(TRUE, FALSE)); +Json_Serialize(Jbin_Array(TRUE, FALSE)) +[true,false] +SELECT Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL); +Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL) +{"qty":56,"price":3.141600,"truc":"machin","garanty":null} +SELECT Json_Serialize(Jbin_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL)); +Json_Serialize(Jbin_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL)) +{"qty":56,"price":3.141600,"truc":"machin","garanty":null} +SELECT Jbin_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty'); +ERROR HY000: Can't initialize function 'jbin_object_key'; This function must have an even number of arguments +SELECT Json_Object_Add(Jbin_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color); +Json_Object_Add(Jbin_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color) +{"qty":56,"price":3.141600,"truc":"machin","garanty":null,"color":"blue"} +SELECT Json_Object_Add(Jbin_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price); +Json_Object_Add(Jbin_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price) +{"qty":56,"price":45.990000,"truc":"machin","garanty":null} +SELECT Json_Object_Add(Jbin_Object_Nonull(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color); +Json_Object_Add(Jbin_Object_Nonull(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color) +{"qty":56,"price":3.141600,"truc":"machin","color":"blue"} +SELECT Json_Object_Add(Jbin_Object_Nonull(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price); +Json_Object_Add(Jbin_Object_Nonull(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price) +{"qty":56,"price":45.990000,"truc":"machin"} +# +# Test Jbin file UDF's +# +SELECT Json_Serialize(Jbin_File('gloss.json')); +Json_Serialize(Jbin_File('gloss.json')) +{"glossary":{"title":"example glossary","GlossDiv":{"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}}}} +SELECT JsonLocate(Jbin_File('gloss.json'),'XML'); +JsonLocate(Jbin_File('gloss.json'),'XML') +glossary:GlossDiv:GlossList:GlossEntry:GlossDef:GlossSeeAlso:[1] +SELECT Json_Object_Key('first', 'foo', 'second', Jbin_Array('a', 33)); +Json_Object_Key('first', 'foo', 'second', Jbin_Array('a', 33)) +{"first":"foo","second":["a",33]} +SELECT Json_Get_Item(Json_Array('a','b','c'), '[1]'); +Json_Get_Item(Json_Array('a','b','c'), '[1]') +NULL +SELECT Json_Get_Item(Json_Object('foo' AS "first", Json_Array('a', 33) AS "json_second"), 'second') AS "item"; +item +["a",33] +SELECT Json_Get_Item(Jbin_Object('foo' first, Jbin_Array('a', 33) jbin_second), 'second:*') item; +item +["a",33] +SELECT Json_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv'); +Json_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv') +{"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}} +SELECT Json_Serialize(Jbin_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv')); +Json_Serialize(Jbin_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv')) +{"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}} +SELECT Json_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv:*'); +Json_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv:*') +{"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}} +SELECT JsonGet_String(Json_File('gloss.json'),'glossary:GlossDiv:GlossList:GlossEntry:GlossDef:GlossSeeAlso') lang; +lang +GML +SELECT Json_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv:GlossList:GlossEntry:GlossDef:GlossSeeAlso') "See also"; +See also +["GML","XML"] +SELECT Json_Serialize(Jbin_Get_Item(Jbin_File('gloss.json'),'glossary:GlossDiv:GlossList:GlossEntry:GlossDef:GlossSeeAlso')) "See also"; +See also +["GML","XML"] +SELECT JsonGet_String(Json_Get_Item(Json_File('gloss.json'),'glossary:GlossDiv:GlossList:GlossEntry:GlossDef:GlossSeeAlso'),'[0]') lang; +lang +GML +# +# Test Item Get/Set/Insert/Update UDF's +# +SELECT Json_Get_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), '[]'); +Json_Get_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), '[]') +[1,2,{"trois":3,"quatre":4}] +SELECT Json_Get_Item(Jbin_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), '[1]'); +Json_Get_Item(Jbin_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), '[1]') +NULL +SELECT Json_Get_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), '[1]'); +Json_Get_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), '[1]') +NULL +SELECT Json_Set_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4))); +Json_Set_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4))) +[1,2,{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 'foo'); +ERROR HY000: Can't initialize function 'json_set_item'; This function must have an odd number of arguments +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq') +[1,2,{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 7, '[1]'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 7, '[1]') +[1,7,{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 7, '[1]'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 7, '[1]') +[1,7,{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Json_Array(7, 8, 9), '[1]'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Json_Array(7, 8, 9), '[1]') +[1,[7,8,9],{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[2]'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[2]') +[1,2,[7,8,9]] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[2]:*'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[2]:*') +[1,2,{"trois":3,"quatre":4}] +Warnings: +Warning 1105 Invalid specification * in a write path +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 3.1416, 'foo'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 3.1416, 'foo') +[1,2,{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 'toto', '[1]:[2]'); +Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 'toto', '[1]:[2]') +[1,[7,8,"toto"],{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 300, '[2]:nxt:total:[]'); +Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 300, '[2]:nxt:total:[]') +[1,[7,8,9],{"trois":3,"quatre":4,"nxt":{"total":[300]}}] +SELECT Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 300, '[2]:nxt:total:[]'); +Json_Set_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 300, '[2]:nxt:total:[]') +[1,[7,8,9,10],{"trois":3,"quatre":4,"cinq":5,"nxt":{"total":[300]}}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[1]', 5, '[2]:cinq', 10, '[1]:[]'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), Jbin_Array(7, 8, 9), '[1]', 5, '[2]:cinq', 10, '[1]:[]') +[1,[7,8,9,10],{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 44, '[2]:quatre'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 44, '[2]:quatre') +[1,2,{"trois":3,"quatre":44}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, 'truc'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, 'truc') +[1,2,{"trois":3,"quatre":4}] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, ''); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '') +[1,2,{"trois":3,"quatre":4},5] +SELECT Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '*'); +Json_Set_Item(Jbin_Array(1, 2, Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '*') +[1,2,{"trois":3,"quatre":4}] +Warnings: +Warning 1105 Invalid specification * in a write path +SELECT Json_Serialize(Jbin_Set_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq')); +Json_Serialize(Jbin_Set_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq')) +[1,2,{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Insert_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq'); +Json_Insert_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq') +[1,2,{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Update_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq'); +Json_Update_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq') +[1,2,{"trois":3,"quatre":4}] +SELECT Json_Insert_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 44, '[2]:quatre'); +Json_Insert_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 44, '[2]:quatre') +[1,[7,8,9,10],{"trois":3,"quatre":4,"cinq":5}] +SELECT Json_Update_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 44, '[2]:quatre'); +Json_Update_Item(Jbin_Array(1, Jbin_Array(7, 8, 9), Jbin_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[]', 44, '[2]:quatre') +[1,[7,8,9],{"trois":3,"quatre":44}] +SELECT Json_Insert_Item(Json_Array(1, Json_Array(7, 8, 9), Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[1]', 300, '[2]:nxt:total:[]'); +Json_Insert_Item(Json_Array(1, Json_Array(7, 8, 9), Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[1]', 300, '[2]:nxt:total:[]') +[1,[7,8,9],{"trois":3,"quatre":4,"cinq":5,"nxt":{"total":[300]}}] +SELECT Json_Update_Item(Json_Array(1, Json_Array(7, 8, 9), Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[1]', 300, '[2]:nxt:total:[]'); +Json_Update_Item(Json_Array(1, Json_Array(7, 8, 9), Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[2]:cinq', 10, '[1]:[1]', 300, '[2]:nxt:total:[]') +[1,[7,10,9],{"trois":3,"quatre":4}] +SELECT Json_Insert_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[]'); +Json_Insert_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[]') +[1,2,{"trois":3,"quatre":4},5] +SELECT Json_Update_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[]'); +Json_Update_Item(Json_Array(1, 2, Json_Object_Key('trois', 3, 'quatre', 4)), 5, '[]') +[1,2,{"trois":3,"quatre":4}] +# +# Test merging items UDF's +# +SELECT Json_Item_Merge(Jbin_Array('a','b','c'), Jbin_Array('d','e','f')); +Json_Item_Merge(Jbin_Array('a','b','c'), Jbin_Array('d','e','f')) +["a","b","c","d","e","f"] +SELECT Json_Item_Merge(Json_Array('a','b','c'), Json_Array('d','e','f')) AS "Result"; +Result +["a","b","c","d","e","f"] +SELECT Json_Array_Add(Jbin_Item_Merge(Jbin_Array('a','b','c'), Jbin_Array('d','e','f')), 'and', 3); +Json_Array_Add(Jbin_Item_Merge(Jbin_Array('a','b','c'), Jbin_Array('d','e','f')), 'and', 3) +["a","b","c","and","d","e","f"] +SELECT Json_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "d",5 "e",6 "f")); +Json_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "d",5 "e",6 "f")) +{"a":1,"b":2,"c":3,"d":4,"e":5,"f":6} +SELECT Json_Item_Merge(Jbin_Object(1 "a",2 "b",2 "c"), Jbin_Array('d','e','f')); +Json_Item_Merge(Jbin_Object(1 "a",2 "b",2 "c"), Jbin_Array('d','e','f')) +Binary Json object +Warnings: +Warning 1105 Second argument is not an object +SELECT Json_Object_Add(Jbin_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "d",5 "e",6 "f")), 'x' AS "and"); +Json_Object_Add(Jbin_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "d",5 "e",6 "f")), 'x' AS "and") +{"a":1,"b":2,"c":3,"d":4,"e":5,"f":6,"and":"x"} +SELECT Json_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "a",5 "e",6 "f")); +Json_Item_Merge(Jbin_Object(1 "a",2 "b",3 "c"), Jbin_Object(4 "a",5 "e",6 "f")) +{"a":4,"b":2,"c":3,"e":5,"f":6} +SELECT Json_Item_Merge('foo', Json_Array('d','e','f')); +ERROR HY000: Can't initialize function 'json_item_merge'; First argument must be a json item +# +# Test making file UDF's +# +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_File('bt1.json'); +Json_File('bt1.json') +[ + "a", + "b", + "c" +] + +SELECT Json_File(Jfile_Make(Jbin_File('bt1.json'), 0)); +Json_File(Jfile_Make(Jbin_File('bt1.json'), 0)) +"a" +"b" +"c" + +SELECT Json_File(Jfile_Make(Jbin_File('bt1.json'), 1)); +Json_File(Jfile_Make(Jbin_File('bt1.json'), 1)) +[ + "a", + "b", + "c" +] + +SELECT Json_File(Jfile_Make(Jbin_File('bt1.json'), 2)); +Json_File(Jfile_Make(Jbin_File('bt1.json'), 2)) +[ + "a", + "b", + "c" +] + +SELECT Json_File('bt1.json', 0); +Json_File('bt1.json', 0) +["a","b","c"] +Warnings: +Warning 1105 File pretty format doesn't match the specified pretty value +SELECT Json_File('bt1.json', 1); +Json_File('bt1.json', 1) +["a","b","c"] +Warnings: +Warning 1105 File pretty format doesn't match the specified pretty value +SELECT Json_File('bt1.json', 2); +Json_File('bt1.json', 2) +["a","b","c"] +SELECT Json_Serialize(Jbin_Array('a','b','c')); +Json_Serialize(Jbin_Array('a','b','c')) +["a","b","c"] +SELECT Json_Serialize(Jbin_Array_Add(Jbin_File('not_exist.json'), 'd')); +Json_Serialize(Jbin_Array_Add(Jbin_File('not_exist.json'), 'd')) +Null json tree +Warnings: +Warning 1105 Open(map) error 2 on not_exist.json +Warning 1105 First argument is not an array +# This does not modify the file +SELECT Json_Serialize(Jbin_Array_Add(Jbin_File('bt1.json'), 'd')); +Json_Serialize(Jbin_Array_Add(Jbin_File('bt1.json'), 'd')) +["a","b","c","d"] +SELECT Json_File('bt1.json', 2); +Json_File('bt1.json', 2) +["a","b","c"] +# This does modify the file +SELECT Json_Array_Add(Jbin_File('bt1.json'), 'd'); +Json_Array_Add(Jbin_File('bt1.json'), 'd') +bt1.json +SELECT Json_File('bt1.json', 2); +Json_File('bt1.json', 2) +["a","b","c","d"] +# Back to the original file +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_Object(Jbin_Array_Add(Jbin_Array('a','b','c'), 'd') "Jbin_foo") AS "Result"; +Result +{"foo":["a","b","c","d"]} +SELECT Json_Object(Jbin_Array_Add(Jbin_File('bt1.json'), 'd')) AS "Result"; +Result +{"Array_Add(Jbin_File('bt1.json'), 'd')":["a","b","c","d"]} +SELECT Json_Object(Jbin_Array_Add(Jbin_File('bt1.json'), 'd') "Jbin_bt1") AS "Result"; +Result +{"bt1":["a","b","c","d"]} +# This does modify the file +SELECT Json_Object(Json_Array_Add(Jbin_File('bt1.json'), 'd') "Jfile_bt1") AS "Result"; +Result +{"bt1":["a","b","c","d"]} +SELECT Json_File('bt1.json'); +Json_File('bt1.json') +[ + "a", + "b", + "c", + "d" +] + +SELECT Json_File(Json_Array_Delete(Jbin_File('bt1.json'), 3), 2); +Json_File(Json_Array_Delete(Jbin_File('bt1.json'), 3), 2) +["a","b","c"] +SELECT Json_Object(Jbin_Array_Add(Jbin_File('bt1.json'), 'd') "Jbin_bt1", n "t1") AS "Result" from t1; +Result +{"bt1":["a","b","c","d"],"t1":1} +{"bt1":["a","b","c","d"],"t1":2} +{"bt1":["a","b","c","d"],"t1":3} +SELECT Json_File(Json_Array_Add(Jbin_Array_Add(Jbin_File('bt1.json'), 'd'), 'e')) AS "Result"; +Result +[ + "a", + "b", + "c", + "d", + "e" +] + +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_File(Json_Array_Add(Jbin_Array_Add(Jbin_File('bt1.json'), 'd'), 'e')) AS "Result" from t1; +Result +[ + "a", + "b", + "c", + "d", + "e" +] + +[ + "a", + "b", + "c", + "d", + "e" +] + +[ + "a", + "b", + "c", + "d", + "e" +] + +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_Array_Add(Jbin_Array_Add(Jbin_File('bt1.json'), 'd'), n) AS "Result" from t1; +Result +bt1.json +bt1.json +bt1.json +# Show modified file +SELECT Json_File('bt1.json'); +Json_File('bt1.json') +[ + "a", + "b", + "c", + "d", + 1, + "d", + 2, + "d", + 3 +] + +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_Array_Add(Jbin_File('bt1.json'), n) AS "Result" from t1; +Result +bt1.json +bt1.json +bt1.json +# Show modified file +SELECT Json_File('bt1.json'); +Json_File('bt1.json') +[ + "a", + "b", + "c", + 1, + 2, + 3 +] + +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +SELECT Json_File(Jbin_Item_Merge(Jbin_File('bt1.json'), Jbin_Array('d','e','f'))); +Json_File(Jbin_Item_Merge(Jbin_File('bt1.json'), Jbin_Array('d','e','f'))) +[ + "a", + "b", + "c" +] + +SELECT Json_File(Json_Item_Merge(Jbin_File('bt1.json'), Jbin_Array('d','e','f'))); +Json_File(Json_Item_Merge(Jbin_File('bt1.json'), Jbin_Array('d','e','f'))) +[ + "a", + "b", + "c", + "d", + "e", + "f" +] + +SELECT Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json'); +Jfile_Make(Jbin_Array('a','b','c'), 'bt1.json') +bt1.json +# Test DELETE from file +SELECT Json_Object(Jbin_Array_Delete(Jbin_File('bt1.json'), 1)) AS "Result"; +Result +{"Array_Delete(Jbin_File('bt1.json'), 1)":["a","c"]} +SELECT Json_Object(Jbin_Array_Delete(Jbin_File('bt1.json'), 2) "Jbin_bt1") AS "Result"; +Result +{"bt1":["a","b"]} +SELECT Json_Object(Jbin_Array_Delete(Jbin_File('bt1.json'), 0) "Jbin_bt1", n "t1") AS "Result" from t1; +Result +{"bt1":["b","c"],"t1":1} +{"bt1":["b","c"],"t1":2} +{"bt1":["b","c"],"t1":3} +SELECT Json_Object(Jbin_Array_Delete(Jbin_File('bt1.json'), 3 - n) "Jbin_bt1") AS "Result" from t1; +Result +{"bt1":["a","b"]} +{"bt1":["a"]} +{"bt1":[]} +SELECT Json_Object(Json_Array_Delete(Jbin_File('bt1.json'), 3 - n) "Jbin_bt1") AS "Result" from t1; +Result +{"bt1":["a","b"]} +{"bt1":["a"]} +{"bt1":[]} +# Show modified file +SELECT Json_File('bt1.json'); +Json_File('bt1.json') +[ + +] + +# Object file +SELECT Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0); +Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0) +bt2.json +SELECT Json_File('bt2.json', 0); +Json_File('bt2.json', 0) +{"a":1,"b":2,"c":3} +SELECT Json_File('bt2.json'); +Json_File('bt2.json') +{"a":1,"b":2,"c":3} + +SELECT Json_Serialize(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d")); +Json_Serialize(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d")) +{"a":1,"b":2,"c":3,"d":4} +# First query (file not modified) +SELECT Json_Object(Jbin_Object_Add(Jbin_File('bt2.json'), 4 AS "d") AS "Jbin_new") AS "Result"; +Result +{"new":{"a":1,"b":2,"c":3,"d":4}} +# First query (file modified) +SELECT Json_Object(Json_Object_Add(Jbin_File('bt2.json'), 4 AS "d") AS "Jfile_new") AS "Result"; +Result +{"new":{"a":1,"b":2,"c":3,"d":4}} +SELECT Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0); +Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0) +bt2.json +SELECT Json_Object(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d") "Jbin_new", n "t1") AS "Result" from t1; +Result +{"new":{"a":1,"b":2,"c":3,"d":4},"t1":1} +{"new":{"a":1,"b":2,"c":3,"d":4},"t1":2} +{"new":{"a":1,"b":2,"c":3,"d":4},"t1":3} +SELECT Json_File(Json_Object_Add(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d"), 5 "e")) AS "Result"; +Result +{"a":1,"b":2,"c":3,"d":4,"e":5} + +SELECT Json_Object_Add(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d"), 5 "e") AS "Result" from t1; +Result +bt2.json +bt2.json +bt2.json +SELECT Json_Object_Add(Jbin_Object_Add(Jbin_File('bt2.json'), 4 "d"), n "n") AS "Result" from t1; +Result +bt2.json +bt2.json +bt2.json +SELECT Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0); +Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0) +bt2.json +SELECT Json_Object_Add(Jbin_File('bt2.json'), n) AS "Result" from t1; +Result +bt2.json +bt2.json +bt2.json +SELECT Json_File('bt2.json'); +Json_File('bt2.json') +{"a":1,"b":2,"c":3,"n":3} + +SELECT Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0); +Jfile_Make(Jbin_Object(1 "a", 2 "b", 3 "c"), 'bt2.json', 0) +bt2.json +SELECT Json_Serialize(Jbin_Item_Merge(Jbin_File('bt2.json'), Jbin_Object(4 "d",5 "e",6 "f"))) AS "Result"; +Result +{"a":1,"b":2,"c":3,"d":4,"e":5,"f":6} +SELECT Json_File(Json_Item_Merge(Jbin_File('bt2.json'), Jbin_Object(4 "d",5 "e",6 "f"))) AS "Result"; +Result +{"a":1,"b":2,"c":3,"d":4,"e":5,"f":6} + +SELECT Json_Item_Merge(Json_Object(1 "a", 2 "b", 3 "c"), Json_Object(4 "d",5 "b",6 "f")) AS "Result"; +Result +{"a":1,"b":5,"c":3,"d":4,"f":6} +SELECT Json_Object(Json_Object_Delete(Jbin_File('bt2.json'), 'b')) AS "Result"; +Result +{"Object_Delete(Jbin_File('bt2.json'), 'b')":{"a":1,"c":3,"d":4,"e":5,"f":6}} +SELECT Json_Object(Jbin_Object_Delete(Jbin_File('bt2.json'), 'c') "Jbin_bt1") AS "Result"; +Result +{"bt1":{"a":1,"d":4,"e":5,"f":6}} +SELECT Json_Object(Json_Object_Delete(Jbin_File('bt2.json'), 'c') "Jbin_bt1") AS "Result"; +Result +{"bt1":{"a":1,"d":4,"e":5,"f":6}} +SELECT Json_Object(Json_Object_Delete(Jbin_File('bt2.json'), 'c') "Jfile_bt1") AS "Result"; +Result +{"bt1":{"a":1,"d":4,"e":5,"f":6}} +SELECT Json_Object(Json_Object_Delete(Jbin_File('bt2.json'), 'a') "Jbin_bt1", n "t1") AS "Result" from t1; +Result +{"bt1":{"d":4,"e":5,"f":6},"t1":1} +{"bt1":{"d":4,"e":5,"f":6},"t1":2} +{"bt1":{"d":4,"e":5,"f":6},"t1":3} +SELECT Json_Serialize(Jbin_Object_List(Jbin_File('bt2.json'))) "Key list"; +Key list +["d","e","f"] +SELECT Jfile_Make('{"a":1, "b":[44, 55]}' json_, 'bt3.json', 0); +Jfile_Make('{"a":1, "b":[44, 55]}' json_, 'bt3.json', 0) +bt3.json +SELECT Json_Array_Add(Json_File('bt3.json', 'b'), 66); +Json_Array_Add(Json_File('bt3.json', 'b'), 66) +[44,55,66] +SELECT Json_Array_Add(Json_File('bt3.json'), 66, 'b'); +Json_Array_Add(Json_File('bt3.json'), 66, 'b') +{"a":1,"b":[44,55,66]} +SELECT Json_Array_Add(Jbin_File('bt3.json', 'b'), 66); +Json_Array_Add(Jbin_File('bt3.json', 'b'), 66) +bt3.json +SELECT Json_File('bt3.json', 3); +Json_File('bt3.json', 3) +{"a":1,"b":[44,55,66]} +SELECT Jfile_Make('{"a":1, "b":[44, 55]}' json_, 'bt3.json', 0); +Jfile_Make('{"a":1, "b":[44, 55]}' json_, 'bt3.json', 0) +bt3.json +CREATE TABLE t2 ( +n INT KEY, +jfile_cols CHAR(12) NOT NULL) +ENGINE= MYISAM; +INSERT INTO t2 VALUES(1,'bt3.json'); +# In this table, the jfile_cols column just contains a file name +UPDATE t2 SET jfile_cols = Json_Array_Add(Jbin_File('bt3.json', 'b'), 66) WHERE n = 1; +SELECT JsonGet_String(jfile_cols, '*') FROM t2; +JsonGet_String(jfile_cols, '*') +{"a":1,"b":[44,55,66]} +UPDATE t2 SET jfile_cols = Json_Insert_Item(jfile_cols, 77, 'b:[]') WHERE n = 1; +SELECT JsonGet_String(jfile_cols, 'b:*') FROM t2; +JsonGet_String(jfile_cols, 'b:*') +[44,55,66,77] +UPDATE t2 SET jfile_cols = Json_Insert_Item(Jbin_Insert_Item(jfile_cols, 88, 'b:') , 99, 'b:') WHERE n = 1; +SELECT JsonGet_String(jfile_cols, '*') FROM t2; +JsonGet_String(jfile_cols, '*') +{"a":1,"b":[44,55,66,77,88,99]} +DROP TABLE t1, t2; |