CREATE TABLE t1 ENGINE=CONNECT CHARSET=latin1 TABLE_TYPE=VIR BLOCK_SIZE=5;
#
# Test UDF's with constant arguments
#
SELECT BsonValue(56, 3.1416, 'foo', NULL);
ERROR HY000: Can't initialize function 'bsonvalue'; Cannot accept more than 1 argument
SELECT BsonValue(3.1416);
BsonValue(3.1416)
3.1416
SELECT BsonValue(-80);
BsonValue(-80)
-80
SELECT BsonValue('foo');
BsonValue('foo')
foo
SELECT BsonValue(9223372036854775807);
BsonValue(9223372036854775807)
9223372036854775807
SELECT BsonValue(NULL);
BsonValue(NULL)
null
SELECT BsonValue(TRUE);
BsonValue(TRUE)
true
SELECT BsonValue(FALSE);
BsonValue(FALSE)
false
SELECT BsonValue();
BsonValue()
null
SELECT BsonValue('[11, 22, 33]' json_) FROM t1;
BsonValue('[11, 22, 33]' json_)
[11,22,33]
[11,22,33]
[11,22,33]
[11,22,33]
[11,22,33]
SELECT Bson_Make_Array();
Bson_Make_Array()
[]
SELECT Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL);
Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL)
[56,3.1416,"My name is \"Foo\"",null]
SELECT Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE);
Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE)
[[56,3.1416,"foo"],true]
SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL)) Array;
ERROR HY000: Can't initialize function 'bson_array_add'; This function must have at least 2 arguments
SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL), 'One more') Array;
Array
[56,3.1416,"foo",null,"One more"]
SELECT Bson_Array_Add(BsonValue('one value'), 'One more');
Bson_Array_Add(BsonValue('one value'), 'One more')
["one value","One more"]
SELECT Bson_Array_Add('one value', 'One more');
Bson_Array_Add('one value', 'One more')
["one value","One more"]
SELECT Bson_Array_Add('one value' json_, 'One more');
Bson_Array_Add('one value' json_, 'One more')
["one value","One more"]
SELECT Bson_Array_Add(5 json_, 'One more');
Bson_Array_Add(5 json_, 'One more')
[5,"One more"]
SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0);
Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0)
[4,5,3,8,7,9]
SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 2) Array;
Array
[5,3,4,8,7,9]
SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9);
Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9)
[5,3,8,7,9,4]
SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1);
Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1)
[1,2,[11,22],"[2]"]
SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1);
Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1)
[1,2,[11,33,22]]
SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]');
Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]')
[1,2,[11,33,22]]
SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin', NULL), 'One more', 'Two more') Array;
Array
[56,3.1416,"machin",null,"One more","Two more"]
SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), 'One more', 'Two more') Array FROM t1;
Array
[56,3.1416,"machin","One more","Two more"]
[56,3.1416,"machin","One more","Two more"]
[56,3.1416,"machin","One more","Two more"]
[56,3.1416,"machin","One more","Two more"]
[56,3.1416,"machin","One more","Two more"]
SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), n) Array FROM t1;
Array
[56,3.1416,"machin",1]
[56,3.1416,"machin",2]
[56,3.1416,"machin",3]
[56,3.1416,"machin",4]
[56,3.1416,"machin",5]
SELECT Bson_Array_Add_Values(Bson_Make_Array(n, 3.1416, 'machin'), n) Array FROM t1;
Array
[1,3.1416,"machin",1]
[2,3.1416,"machin",2]
[3,3.1416,"machin",3]
[4,3.1416,"machin",4]
[5,3.1416,"machin",5]
SELECT Bson_Array_Add_Values('[56]', 3.1416, 'machin') Array;
Array
[56,3.1416,"machin"]
SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0);
Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0)
[3.1416,"My name is \"Foo\"",null]
SELECT Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2);
Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2)
{"56":56,"3.1416":3.1416,"My name is Foo":"My name is Foo","NULL":null}
Warnings:
Warning 1105 First argument target is not an array
SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2');
Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2')
[56,3.1416,"My name is \"Foo\"",null]
Warnings:
Warning 1105 Missing or null array index
SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2);
Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2)
[56,3.1416,"My name is \"Foo\"",null]
Warnings:
Warning 1105 First argument target is not an array
/* WARNING VOID */
#
SELECT Bson_Make_Object(56, 3.1416, 'foo', NULL);
Bson_Make_Object(56, 3.1416, 'foo', NULL)
{"56":56,"3.1416":3.1416,"foo":"foo","NULL":null}
SELECT Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty);
Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty)
{"qty":56,"price":3.1416,"truc":"foo","garanty":null}
SELECT Bson_Make_Object();
Bson_Make_Object()
{}
SELECT Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL);
Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL)
{"Make_Array(56, 3.1416, 'foo')":[56,3.1416,"foo"],"NULL":null}
SELECT Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL);
Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL)
[{"qty":56,"price":3.1416,"foo":"foo"},null]
SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL);
Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL)
{"qty":56,"price":3.1416,"truc":"machin","garanty":null}
SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty');
ERROR HY000: Can't initialize function 'bson_object_key'; This function must have an even number of arguments
SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'blue' color);
Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'blue' color)
{"qty":56,"price":3.1416,"truc":"machin","garanty":null,"color":"blue"}
SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  45.99 price);
Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  45.99 price)
{"qty":56,"price":45.99,"truc":"machin","garanty":null}
SELECT Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1);
Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1)
NULL
Warnings:
Warning 1105 Error 2 opening notexist.json
Warning 1105 No sub-item at '[1]'
SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'truc');
Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'truc')
{"qty":56,"price":3.1416,"garanty":null}
SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'chose');
Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty),  'chose')
{"qty":56,"price":3.1416,"truc":"machin","garanty":null}
SELECT Bson_Object_List(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc,  NULL garanty)) "Key List";
Key List
["qty","price","truc","garanty"]
SELECT Bson_Object_List('{"qty":56, "price":3.1416, "truc":"machin", "garanty":null}') "Key List";
Key List
["qty","price","truc","garanty"]
SELECT Bson_Object_Values('{"One":1,"Two":2,"Three":3}') "Value List";
Value List
[1,2,3]
#
# Test UDF's with column arguments
#
SELECT Bsonset_Def_Prec(2);
Bsonset_Def_Prec(2)
2
CREATE TABLE t2
(
ISBN CHAR(15),
LANG CHAR(2),
SUBJECT CHAR(32),
AUTHOR CHAR(64),
TITLE CHAR(32),
TRANSLATION CHAR(32),
TRANSLATOR CHAR(80),
PUBLISHER CHAR(32),
DATEPUB int(4)
) ENGINE=CONNECT CHARSET=latin1 TABLE_TYPE=BSON FILE_NAME='biblio.json';
SELECT Bson_Make_Array(AUTHOR, TITLE, DATEPUB) FROM t2;
Bson_Make_Array(AUTHOR, TITLE, DATEPUB)
[" Jean-Christophe Bernadac, François Knab","Construire une application XML",1999]
["William J. Pardi","XML en Action",1999]
SELECT Bson_Make_Object(AUTHOR, TITLE, DATEPUB) FROM t2;
Bson_Make_Object(AUTHOR, TITLE, DATEPUB)
{"AUTHOR":" Jean-Christophe Bernadac, François Knab","TITLE":"Construire une application XML","DATEPUB":1999}
{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999}
SELECT Bson_Array_Grp(TITLE, DATEPUB) FROM t2;
ERROR HY000: Can't initialize function 'bson_array_grp'; This function can only accept 1 argument
SELECT Bson_Array_Grp(TITLE) FROM t2;
Bson_Array_Grp(TITLE)
["Construire une application XML","XML en Action"]
CREATE TABLE t3 (
SERIALNO CHAR(5) NOT NULL,
NAME VARCHAR(12) NOT NULL FLAG=6,
SEX SMALLINT(1) NOT NULL,
TITLE VARCHAR(15) NOT NULL FLAG=20,
MANAGER CHAR(5) DEFAULT NULL,
DEPARTMENT CHAr(4) NOT NULL FLAG=41,
SECRETARY CHAR(5) DEFAULT NULL FLAG=46,
SALARY DOUBLE(8,2) NOT NULL FLAG=52
) ENGINE=CONNECT CHARSET=latin1 TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1;
SELECT Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY) FROM t3 WHERE NAME = 'MERCHANT';
Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)
{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.00}
SELECT DEPARTMENT, Bson_Array_Grp(NAME) FROM t3 GROUP BY DEPARTMENT;
DEPARTMENT Bson_Array_Grp(NAME)
0021 ["STRONG","SHORTSIGHT"]
0318 ["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]
0319 ["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL"]
2452 ["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]
Warnings:
Warning 1105 Result truncated to json_grp_size values
SELECT BsonSet_Grp_Size(30);
BsonSet_Grp_Size(30)
30
SELECT Bson_Make_Object(title, Bson_Array_Grp(name) `json_names`) from t3 GROUP BY title;
Bson_Make_Object(title, Bson_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 Bson_Make_Array(DEPARTMENT, Bson_Array_Grp(NAME)) FROM t3 GROUP BY DEPARTMENT;
Bson_Make_Array(DEPARTMENT, Bson_Array_Grp(NAME))
["0021",["STRONG","SHORTSIGHT"]]
["0318",["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]]
["0319",["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]]
["2452",["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]]
SELECT Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(NAME) json_NAMES) FROM t3 GROUP BY DEPARTMENT;
Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(NAME) json_NAMES)
{"DEPARTMENT":"0021","NAMES":["STRONG","SHORTSIGHT"]}
{"DEPARTMENT":"0318","NAMES":["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]}
{"DEPARTMENT":"0319","NAMES":["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]}
{"DEPARTMENT":"2452","NAMES":["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]}
SELECT Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT;
Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES)
{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.00},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.00}]}
{"DEPARTMENT":"0318","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","TITLE":"SALESMAN","SALARY":9600.00},{"SERIALNO":"24888","NAME":"PLUMHEAD","TITLE":"TYPIST","SALARY":2800.00},{"SERIALNO":"27845","NAME":"HONEY","TITLE":"SECRETARY","SALARY":4900.00},{"SERIALNO":"73452","NAME":"TONGHO","TITLE":"ENGINEER","SALARY":6800.00},{"SERIALNO":"74234","NAME":"WALTER","TITLE":"ENGINEER","SALARY":7400.00},{"SERIALNO":"77777","NAME":"SHRINKY","TITLE":"ADMINISTRATOR","SALARY":7500.00},{"SERIALNO":"70012","NAME":"WERTHER","TITLE":"DIRECTOR","SALARY":14500.00},{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.00},{"SERIALNO":"73111","NAME":"WHEELFOR","TITLE":"SALESMAN","SALARY":10030.00}]}
{"DEPARTMENT":"0319","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","TITLE":"SALESMAN","SALARY":14800.00},{"SERIALNO":"40567","NAME":"QUINN","TITLE":"DIRECTOR","SALARY":14000.00},{"SERIALNO":"00137","NAME":"BROWNY","TITLE":"ENGINEER","SALARY":10500.00},{"SERIALNO":"12345","NAME":"KITTY","TITLE":"TYPIST","SALARY":3000.45},{"SERIALNO":"33333","NAME":"MONAPENNY","TITLE":"SECRETARY","SALARY":3800.00},{"SERIALNO":"00023","NAME":"MARTIN","TITLE":"ENGINEER","SALARY":10000.00},{"SERIALNO":"07654","NAME":"FUNNIGUY","TITLE":"ADMINISTRATOR","SALARY":8500.00},{"SERIALNO":"45678","NAME":"BUGHAPPY","TITLE":"PROGRAMMER","SALARY":8500.00},{"SERIALNO":"56789","NAME":"FODDERMAN","TITLE":"SALESMAN","SALARY":7000.00},{"SERIALNO":"55555","NAME":"MESSIFUL","TITLE":"SECRETARY","SALARY":5000.50},{"SERIALNO":"98765","NAME":"GOOSEPEN","TITLE":"ADMINISTRATOR","SALARY":4700.00}]}
{"DEPARTMENT":"2452","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","TITLE":"SCIENTIST","SALARY":8000.00},{"SERIALNO":"31416","NAME":"ORELLY","TITLE":"ENGINEER","SALARY":13400.00},{"SERIALNO":"36666","NAME":"BIGHORN","TITLE":"SCIENTIST","SALARY":11000.00},{"SERIALNO":"02345","NAME":"SMITH","TITLE":"ENGINEER","SALARY":9000.00},{"SERIALNO":"11111","NAME":"CHERRY","TITLE":"SECRETARY","SALARY":4500.00}]}
SELECT Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT, TITLE;
Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES)
{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.00}]}
{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.00}]}
{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.00}]}
{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.00}]}
{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.00},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.00}]}
{"DEPARTMENT":"0318","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","SALARY":9600.00},{"SERIALNO":"78943","NAME":"MERCHANT","SALARY":8700.00},{"SERIALNO":"73111","NAME":"WHEELFOR","SALARY":10030.00}]}
{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.00}]}
{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.00}]}
{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.00},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.00}]}
{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.00}]}
{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.00},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.00}]}
{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.00}]}
{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.00},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.00}]}
{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.00},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.50}]}
{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.45}]}
{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.00},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.00}]}
{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.00},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.00}]}
{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.00}]}
SELECT Bson_Object_Grp(SALARY) FROM t3;
ERROR HY000: Can't initialize function 'bson_object_grp'; This function requires 2 arguments (key, value)
SELECT Bson_Object_Grp(NAME, SALARY) FROM t3;
Bson_Object_Grp(NAME, SALARY)
{"BANCROFT":9600.00,"SMITH":9000.00,"MERCHANT":8700.00,"FUNNIGUY":8500.00,"BUGHAPPY":8500.00,"BIGHEAD":8000.00,"SHRINKY":7500.00,"WALTER":7400.00,"FODDERMAN":7000.00,"TONGHO":6800.00,"SHORTSIGHT":5500.00,"MESSIFUL":5000.50,"HONEY":4900.00,"GOOSEPEN":4700.00,"CHERRY":4500.00,"MONAPENNY":3800.00,"KITTY":3000.45,"PLUMHEAD":2800.00,"STRONG":23000.00,"BULLOZER":14800.00,"WERTHER":14500.00,"QUINN":14000.00,"ORELLY":13400.00,"BIGHORN":11000.00,"BROWNY":10500.00,"WHEELFOR":10030.00,"MARTIN":10000.00}
SELECT Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES") FROM t3 GROUP BY DEPARTMENT;
Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES")
{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.00,"SHORTSIGHT":5500.00}}
{"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.00,"PLUMHEAD":2800.00,"HONEY":4900.00,"TONGHO":6800.00,"WALTER":7400.00,"SHRINKY":7500.00,"WERTHER":14500.00,"MERCHANT":8700.00,"WHEELFOR":10030.00}}
{"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.00,"QUINN":14000.00,"BROWNY":10500.00,"KITTY":3000.45,"MONAPENNY":3800.00,"MARTIN":10000.00,"FUNNIGUY":8500.00,"BUGHAPPY":8500.00,"FODDERMAN":7000.00,"MESSIFUL":5000.50,"GOOSEPEN":4700.00}}
{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.00,"ORELLY":13400.00,"BIGHORN":11000.00,"SMITH":9000.00,"CHERRY":4500.00}}
SELECT Bson_Array_Grp(NAME) FROM t3;
Bson_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 Bson_Object_Key(name, title) FROM t3 WHERE DEPARTMENT = 318;
Bson_Object_Key(name, title)
{"BANCROFT":"SALESMAN"}
{"MERCHANT":"SALESMAN"}
{"SHRINKY":"ADMINISTRATOR"}
{"WALTER":"ENGINEER"}
{"TONGHO":"ENGINEER"}
{"HONEY":"SECRETARY"}
{"PLUMHEAD":"TYPIST"}
{"WERTHER":"DIRECTOR"}
{"WHEELFOR":"SALESMAN"}
SELECT Bson_Object_Grp(name, title) FROM t3 WHERE DEPARTMENT = 318;
Bson_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
#
SELECT BsonGet_String(Bson_Array_Grp(name),'[#]') FROM t3;
BsonGet_String(Bson_Array_Grp(name),'[#]')
27
SELECT BsonGet_String(Bson_Array_Grp(name),'[","]') FROM t3;
BsonGet_String(Bson_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 BsonGet_String(Bson_Array_Grp(name),'[>]') FROM t3;
BsonGet_String(Bson_Array_Grp(name),'[>]')
WHEELFOR
SET @j1 = '[45,28,36,45,89]';
SELECT BsonGet_String(@j1,'1');
BsonGet_String(@j1,'1')
28
SELECT BsonGet_String(@j1 json_,'3');
BsonGet_String(@j1 json_,'3')
45
SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3');
BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3')
45
SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'["+"]') "list",'=' as "egal",BsonGet_String(Bson_Make_Array(45,28,36,45,89),'[+]') "sum";
list egal sum
45+28+36+45+89 = 243
SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0');
BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0')
36
SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*');
BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*')
[36,45,89]
SELECT BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc');
BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc')
machin
SET @j2 = '{"qty":56,"price":3.141600,"truc":"machin","garanty":null}';
SELECT BsonGet_String(@j2 json_,'truc');
BsonGet_String(@j2 json_,'truc')
machin
SELECT BsonGet_String(@j2,'truc');
BsonGet_String(@j2,'truc')
machin
SELECT BsonGet_String(@j2,'chose');
BsonGet_String(@j2,'chose')
NULL
SELECT BsonGet_String(NULL json_, NULL);
BsonGet_String(NULL json_, NULL)
NULL
Warnings:
Warning 1105 Argument is NULL
/* NULL WARNING */
SELECT department, BsonGet_String(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department;
department Sumsal
0021 28500.00
0318 72230.00
0319 89800.95
2452 45900.00
SELECT BsonGet_Int(@j1, '4');
BsonGet_Int(@j1, '4')
89
SELECT BsonGet_Int(@j1, '[#]');
BsonGet_Int(@j1, '[#]')
5
SELECT BsonGet_Int(@j1, '[+]');
BsonGet_Int(@j1, '[+]')
243
SELECT BsonGet_Int(@j1 json_, '3');
BsonGet_Int(@j1 json_, '3')
45
SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3');
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3')
45
SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]');
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]')
45
SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]');
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]')
243
SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0');
BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0')
36
SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1');
BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1')
28
SELECT BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty');
BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty')
56
SELECT BsonGet_Int(@j2 json_, 'price');
BsonGet_Int(@j2 json_, 'price')
3
SELECT BsonGet_Int(@j2, 'qty');
BsonGet_Int(@j2, 'qty')
56
SELECT BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose');
BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose')
NULL
SELECT BsonGet_Int(BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)), '1.*'), '[+]') sum;
sum
170
SELECT department, BsonGet_Int(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"), 'salaries.[+]') Sumsal FROM t3 GROUP BY department;
department Sumsal
0021 28500
0318 72230
0319 89800
2452 45900
SELECT BsonGet_Real(@j1, '2');
BsonGet_Real(@j1, '2')
36.000000000000000
SELECT BsonGet_Real(@j1 json_, '3', 2);
BsonGet_Real(@j1 json_, '3', 2)
45.00
SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3');
BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3')
45.000000000000000
SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]');
BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]')
45.000000000000000
SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]');
BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]')
243.000000000000000
SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]');
BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]')
48.600000000000000
SELECT BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0');
BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0')
36.000000000000000
SELECT BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price');
BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price')
3.141600000000000
SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty');
BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty')
56.000000000000000
SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price');
BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price')
3.141600000000000
SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4);
BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4)
3.1416
SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose');
BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose')
NULL
SELECT department, BsonGet_Real(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department;
department Sumsal
0021 28500.000000000000000
0318 72230.000000000000000
0319 89800.950000000000000
2452 45900.000000000000000
#
# Documentation examples
#
SELECT
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '4') "Rank",
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[#]') "Number",
BsonGet_String(Bson_Make_Array(45,28,36,45,89), '[","]') "Concat",
BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]') "Sum",
BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]', 2) "Avg";
Rank Number Concat Sum Avg
89 5 45,28,36,45,89 243 48.60
SELECT
BsonGet_String('{"qty":7,"price":29.50,"garanty":null}', 'price') "String",
BsonGet_Int('{"qty":7,"price":29.50,"garanty":null}', 'price') "Int",
BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price') "Real";
String Int Real
29.50 29 29.500000000000000
SELECT BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price', 3) "Real";
Real
29.500
#
# Testing Locate
#
SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin');
BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin')
$.truc
SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56);
BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56)
$.qty
SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416);
BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416)
$.price
SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose');
BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose')
NULL
SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'Jack') Path;
Path
$.AUTHORS[1].FN
SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'jack' ci) Path;
Path
$.AUTHORS[1].FN
SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"Jack", "LN":"London"}' json_) Path;
Path
$.AUTHORS[1]
SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"jack", "LN":"London"}' json_) Path;
Path
NULL
SELECT BsonLocate('[45,28,36,45,89]',36);
BsonLocate('[45,28,36,45,89]',36)
$[2]
SELECT BsonLocate('[45,28,36,45,89]' json_,28.0);
BsonLocate('[45,28,36,45,89]' json_,28.0)
NULL
SELECT Bson_Locate_All('[45,28,36,45,89]',10);
Bson_Locate_All('[45,28,36,45,89]',10)
[]
SELECT Bson_Locate_All('[45,28,36,45,89]',45);
Bson_Locate_All('[45,28,36,45,89]',45)
["$[0]","$[3]"]
SELECT Bson_Locate_All('[[45,28],36,45,89]',45);
Bson_Locate_All('[[45,28],36,45,89]',45)
["$[0][0]","$[2]"]
SELECT Bson_Locate_All('[[45,28,45],36,45,89]',45);
Bson_Locate_All('[[45,28,45],36,45,89]',45)
["$[0][0]","$[0][2]","$[2]"]
SELECT Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]'));
Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]'))
["$[0][0]","$[0][2]","$[2]"]
SELECT BsonLocate('[[45,28,45],36,45,89]',45,n) from t1;
BsonLocate('[[45,28,45],36,45,89]',45,n)
$[0][0]
$[0][2]
$[2]
NULL
NULL
SELECT BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) FROM t1;
BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']'))
$[0][0]
$[0][2]
$[2]
NULL
NULL
SELECT BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) AS `Path` FROM t1 GROUP BY n HAVING `Path` IS NOT NULL;
Path
$[0][0]
$[0][2]
$[2]
SELECT Bson_Locate_All('[45,28,[36,45,89]]',45);
Bson_Locate_All('[45,28,[36,45,89]]',45)
["$[0]","$[2][1]"]
SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0));
Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0))
[]
SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0);
Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0)
["$[1][1]"]
SELECT BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_);
BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_)
$[1]
SELECT BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_);
BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_)
$[0]
SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','45') "All paths";
All paths
[]
SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_);
Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_)
["$[1][0]"]
SELECT BsonGet_Int(Bson_Locate_All('[[45,28],[[36,45],89]]',45), '[#]') "Nb of occurs";
Nb of occurs
2
SELECT Bson_Locate_All('[[45,28],[[36,45],89]]',45,2);
Bson_Locate_All('[[45,28],[[36,45],89]]',45,2)
["$[0][0]"]
SELECT BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0');
BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0')
$[0]
SELECT BsonLocate(Bson_File('test/biblio.json'), 'Knab');
BsonLocate(Bson_File('test/biblio.json'), 'Knab')
$[0].AUTHOR[1].LASTNAME
SELECT Bson_Locate_All('test/biblio.json' jfile_, 'Knab');
Bson_Locate_All('test/biblio.json' jfile_, 'Knab')
["$[0].AUTHOR[1].LASTNAME"]
#
# Testing json files
#
SELECT Bfile_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;
NewFile
test/fx.json
SELECT Bfile_Make('test/fx.json', 1);
Bfile_Make('test/fx.json', 1)
test/fx.json
SELECT Bfile_Make('test/fx.json' jfile_);
Bfile_Make('test/fx.json' jfile_)
test/fx.json
SELECT Bfile_Make(Bbin_File('test/fx.json'), 0);
Bfile_Make(Bbin_File('test/fx.json'), 0)
test/fx.json
SELECT Bson_File('test/fx.json', 1);
Bson_File('test/fx.json', 1)
[{"_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]}]
Warnings:
Warning 1105 File pretty format doesn't match the specified pretty value
SELECT Bson_File('test/fx.json', 2);
Bson_File('test/fx.json', 2)
[{"_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]}]
Warnings:
Warning 1105 File pretty format doesn't match the specified pretty value
SELECT Bson_File('test/fx.json', 0);
Bson_File('test/fx.json', 0)
[{"_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]}]
SELECT Bson_File('test/fx.json', '0');
Bson_File('test/fx.json', '0')
{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]}
SELECT Bson_File('test/fx.json', '[?]');
Bson_File('test/fx.json', '[?]')
NULL
Warnings:
Warning 1105 Invalid function specification ?
SELECT BsonGet_String(Bson_File('test/fx.json'), '1.*');
BsonGet_String(Bson_File('test/fx.json'), '1.*')
{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]}
SELECT BsonGet_String(Bson_File('test/fx.json'), '1');
BsonGet_String(Bson_File('test/fx.json'), '1')
6 car roadster 56000 (6, 9)
SELECT BsonGet_Int(Bson_File('test/fx.json'), '1.mileage') AS Mileage;
Mileage
56000
SELECT BsonGet_Real(Bson_File('test/fx.json'), '0.price', 2) AS Price;
Price
5.65
SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings');
Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings')
{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4,6]}
SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings');
Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings')
{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]}
SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1);
Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1)
{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]}
SELECT Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0);
Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0)
[6,2,4]
SELECT Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1);
Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1)
{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2]}
SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin);
Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin)
{"_id":7,"type":"food","item":"meat","origin":"france","ratings":[2,4]}
SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size');
Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size')
{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]}
Warnings:
Warning 1105 No sub-item at 'size'
SELECT Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size');
Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size')
{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":70},"ratings":[5,8,7]}
SELECT Bson_Object_List(Bson_File('test/fx.json', '3.size'));
Bson_Object_List(Bson_File('test/fx.json', '3.size'))
["W","L","H"]
#
# Testing new functions
#
SELECT Bson_Item_Merge('["a","b","c"]','["d","e","f"]') as "Result";
Result
["a","b","c","d","e","f"]
SELECT Bson_Item_Merge(Bson_Make_Array('a','b','c'), Bson_Make_Array('d','e','f')) as "Result";
Result
["a","b","c","d","e","f"]
SELECT
Bson_Set_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Set",
Bson_Insert_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Insert",
Bson_Update_Item(Bson_Make_Array(1,2,3,Bson_Object_Key('quatre',4)),'foo','$[1]',5,'$[3].cinq') "Update";
Set Insert Update
[1,"foo",3,{"quatre":4,"cinq":5}] [1,2,3,{"quatre":4,"cinq":5}] [1,"foo",3,{"quatre":4}]
SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux');
bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux')
[1,3,{"quatre":4}]
SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]');
bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]')
[1,3,{"quatre":4}]
SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux');
bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux')
[1,2,3,{"quatre":4}]
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
SELECT BsonSet_Grp_Size(10);
BsonSet_Grp_Size(10)
10