1CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=VIR BLOCK_SIZE=5; 2# 3# Test UDF's with constant arguments 4# 5SELECT BsonValue(56, 3.1416, 'foo', NULL); 6ERROR HY000: Can't initialize function 'bsonvalue'; Cannot accept more than 1 argument 7SELECT BsonValue(3.1416); 8BsonValue(3.1416) 93.1416 10SELECT BsonValue(-80); 11BsonValue(-80) 12-80 13SELECT BsonValue('foo'); 14BsonValue('foo') 15foo 16SELECT BsonValue(9223372036854775807); 17BsonValue(9223372036854775807) 189223372036854775807 19SELECT BsonValue(NULL); 20BsonValue(NULL) 21null 22SELECT BsonValue(TRUE); 23BsonValue(TRUE) 24true 25SELECT BsonValue(FALSE); 26BsonValue(FALSE) 27false 28SELECT BsonValue(); 29BsonValue() 30null 31SELECT BsonValue('[11, 22, 33]' json_) FROM t1; 32BsonValue('[11, 22, 33]' json_) 33[11,22,33] 34[11,22,33] 35[11,22,33] 36[11,22,33] 37[11,22,33] 38SELECT Bson_Make_Array(); 39Bson_Make_Array() 40[] 41SELECT Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL); 42Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL) 43[56,3.1416,"My name is \"Foo\"",null] 44SELECT Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE); 45Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE) 46[[56,3.1416,"foo"],true] 47SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL)) Array; 48ERROR HY000: Can't initialize function 'bson_array_add'; This function must have at least 2 arguments 49SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL), 'One more') Array; 50Array 51[56,3.1416,"foo",null,"One more"] 52SELECT Bson_Array_Add(BsonValue('one value'), 'One more'); 53Bson_Array_Add(BsonValue('one value'), 'One more') 54["one value","One more"] 55SELECT Bson_Array_Add('one value', 'One more'); 56Bson_Array_Add('one value', 'One more') 57["one value","One more"] 58SELECT Bson_Array_Add('one value' json_, 'One more'); 59Bson_Array_Add('one value' json_, 'One more') 60["one value","One more"] 61SELECT Bson_Array_Add(5 json_, 'One more'); 62Bson_Array_Add(5 json_, 'One more') 63[5,"One more"] 64SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0); 65Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0) 66[4,5,3,8,7,9] 67SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 2) Array; 68Array 69[5,3,4,8,7,9] 70SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9); 71Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9) 72[5,3,8,7,9,4] 73SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1); 74Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1) 75[1,2,[11,22],"[2]"] 76SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1); 77Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1) 78[1,2,[11,33,22]] 79SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]'); 80Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]') 81[1,2,[11,33,22]] 82SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin', NULL), 'One more', 'Two more') Array; 83Array 84[56,3.1416,"machin",null,"One more","Two more"] 85SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), 'One more', 'Two more') Array FROM t1; 86Array 87[56,3.1416,"machin","One more","Two more"] 88[56,3.1416,"machin","One more","Two more"] 89[56,3.1416,"machin","One more","Two more"] 90[56,3.1416,"machin","One more","Two more"] 91[56,3.1416,"machin","One more","Two more"] 92SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), n) Array FROM t1; 93Array 94[56,3.1416,"machin",1] 95[56,3.1416,"machin",2] 96[56,3.1416,"machin",3] 97[56,3.1416,"machin",4] 98[56,3.1416,"machin",5] 99SELECT Bson_Array_Add_Values(Bson_Make_Array(n, 3.1416, 'machin'), n) Array FROM t1; 100Array 101[1,3.1416,"machin",1] 102[2,3.1416,"machin",2] 103[3,3.1416,"machin",3] 104[4,3.1416,"machin",4] 105[5,3.1416,"machin",5] 106SELECT Bson_Array_Add_Values('[56]', 3.1416, 'machin') Array; 107Array 108[56,3.1416,"machin"] 109SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0); 110Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0) 111[3.1416,"My name is \"Foo\"",null] 112SELECT Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2); 113Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2) 114{"56":56,"3.1416":3.1416,"My name is Foo":"My name is Foo","NULL":null} 115Warnings: 116Warning 1105 First argument target is not an array 117SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2'); 118Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2') 119[56,3.1416,"My name is \"Foo\"",null] 120Warnings: 121Warning 1105 Missing or null array index 122SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2); 123Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2) 124[56,3.1416,"My name is \"Foo\"",null] 125Warnings: 126Warning 1105 First argument target is not an array 127/* WARNING VOID */ 128# 129SELECT Bson_Make_Object(56, 3.1416, 'foo', NULL); 130Bson_Make_Object(56, 3.1416, 'foo', NULL) 131{"56":56,"3.1416":3.1416,"foo":"foo","NULL":null} 132SELECT Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty); 133Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty) 134{"qty":56,"price":3.1416,"truc":"foo","garanty":null} 135SELECT Bson_Make_Object(); 136Bson_Make_Object() 137{} 138SELECT Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL); 139Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL) 140{"Make_Array(56, 3.1416, 'foo')":[56,3.1416,"foo"],"NULL":null} 141SELECT Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL); 142Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL) 143[{"qty":56,"price":3.1416,"foo":"foo"},null] 144SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL); 145Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL) 146{"qty":56,"price":3.1416,"truc":"machin","garanty":null} 147SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty'); 148ERROR HY000: Can't initialize function 'bson_object_key'; This function must have an even number of arguments 149SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color); 150Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color) 151{"qty":56,"price":3.1416,"truc":"machin","garanty":null,"color":"blue"} 152SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price); 153Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price) 154{"qty":56,"price":45.99,"truc":"machin","garanty":null} 155SELECT Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1); 156Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1) 157NULL 158Warnings: 159Warning 1105 Error 2 opening notexist.json 160Warning 1105 No sub-item at '[1]' 161SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc'); 162Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc') 163{"qty":56,"price":3.1416,"garanty":null} 164SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose'); 165Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose') 166{"qty":56,"price":3.1416,"truc":"machin","garanty":null} 167SELECT Bson_Object_List(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty)) "Key List"; 168Key List 169["qty","price","truc","garanty"] 170SELECT Bson_Object_List('{"qty":56, "price":3.1416, "truc":"machin", "garanty":null}') "Key List"; 171Key List 172["qty","price","truc","garanty"] 173SELECT Bson_Object_Values('{"One":1,"Two":2,"Three":3}') "Value List"; 174Value List 175[1,2,3] 176# 177# Test UDF's with column arguments 178# 179SELECT Bsonset_Def_Prec(2); 180Bsonset_Def_Prec(2) 1812 182CREATE TABLE t2 183( 184ISBN CHAR(15), 185LANG CHAR(2), 186SUBJECT CHAR(32), 187AUTHOR CHAR(64), 188TITLE CHAR(32), 189TRANSLATION CHAR(32), 190TRANSLATOR CHAR(80), 191PUBLISHER CHAR(32), 192DATEPUB int(4) 193) ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; 194SELECT Bson_Make_Array(AUTHOR, TITLE, DATEPUB) FROM t2; 195Bson_Make_Array(AUTHOR, TITLE, DATEPUB) 196[" Jean-Christophe Bernadac, Fran�ois Knab","Construire une application XML",1999] 197["William J. Pardi","XML en Action",1999] 198SELECT Bson_Make_Object(AUTHOR, TITLE, DATEPUB) FROM t2; 199Bson_Make_Object(AUTHOR, TITLE, DATEPUB) 200{"AUTHOR":" Jean-Christophe Bernadac, Fran�ois Knab","TITLE":"Construire une application XML","DATEPUB":1999} 201{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} 202SELECT Bson_Array_Grp(TITLE, DATEPUB) FROM t2; 203ERROR HY000: Can't initialize function 'bson_array_grp'; This function can only accept 1 argument 204SELECT Bson_Array_Grp(TITLE) FROM t2; 205Bson_Array_Grp(TITLE) 206["Construire une application XML","XML en Action"] 207CREATE TABLE t3 ( 208SERIALNO CHAR(5) NOT NULL, 209NAME VARCHAR(12) NOT NULL FLAG=6, 210SEX SMALLINT(1) NOT NULL, 211TITLE VARCHAR(15) NOT NULL FLAG=20, 212MANAGER CHAR(5) DEFAULT NULL, 213DEPARTMENT CHAr(4) NOT NULL FLAG=41, 214SECRETARY CHAR(5) DEFAULT NULL FLAG=46, 215SALARY DOUBLE(8,2) NOT NULL FLAG=52 216) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1; 217SELECT Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY) FROM t3 WHERE NAME = 'MERCHANT'; 218Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY) 219{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.00} 220SELECT DEPARTMENT, Bson_Array_Grp(NAME) FROM t3 GROUP BY DEPARTMENT; 221DEPARTMENT Bson_Array_Grp(NAME) 2220021 ["STRONG","SHORTSIGHT"] 2230318 ["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"] 2240319 ["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL"] 2252452 ["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"] 226Warnings: 227Warning 1105 Result truncated to json_grp_size values 228SELECT BsonSet_Grp_Size(30); 229BsonSet_Grp_Size(30) 23030 231SELECT Bson_Make_Object(title, Bson_Array_Grp(name) `json_names`) from t3 GROUP BY title; 232Bson_Make_Object(title, Bson_Array_Grp(name) `json_names`) 233{"title":"ADMINISTRATOR","names":["GOOSEPEN","FUNNIGUY","SHRINKY"]} 234{"title":"DIRECTOR","names":["QUINN","WERTHER","STRONG"]} 235{"title":"ENGINEER","names":["BROWNY","ORELLY","MARTIN","TONGHO","WALTER","SMITH"]} 236{"title":"PROGRAMMER","names":["BUGHAPPY"]} 237{"title":"SALESMAN","names":["WHEELFOR","MERCHANT","BULLOZER","BANCROFT","FODDERMAN"]} 238{"title":"SCIENTIST","names":["BIGHEAD","BIGHORN"]} 239{"title":"SECRETARY","names":["MESSIFUL","HONEY","SHORTSIGHT","CHERRY","MONAPENNY"]} 240{"title":"TYPIST","names":["KITTY","PLUMHEAD"]} 241SELECT Bson_Make_Array(DEPARTMENT, Bson_Array_Grp(NAME)) FROM t3 GROUP BY DEPARTMENT; 242Bson_Make_Array(DEPARTMENT, Bson_Array_Grp(NAME)) 243["0021",["STRONG","SHORTSIGHT"]] 244["0318",["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]] 245["0319",["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]] 246["2452",["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]] 247SELECT Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(NAME) json_NAMES) FROM t3 GROUP BY DEPARTMENT; 248Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(NAME) json_NAMES) 249{"DEPARTMENT":"0021","NAMES":["STRONG","SHORTSIGHT"]} 250{"DEPARTMENT":"0318","NAMES":["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]} 251{"DEPARTMENT":"0319","NAMES":["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]} 252{"DEPARTMENT":"2452","NAMES":["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]} 253SELECT Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT; 254Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) 255{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.00},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.00}]} 256{"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}]} 257{"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}]} 258{"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}]} 259SELECT Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT, TITLE; 260Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) 261{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.00}]} 262{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.00}]} 263{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.00}]} 264{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.00}]} 265{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.00},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.00}]} 266{"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}]} 267{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.00}]} 268{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.00}]} 269{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.00},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.00}]} 270{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.00}]} 271{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.00},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.00}]} 272{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.00}]} 273{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.00},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.00}]} 274{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.00},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.50}]} 275{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.45}]} 276{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.00},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.00}]} 277{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.00},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.00}]} 278{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.00}]} 279SELECT Bson_Object_Grp(SALARY) FROM t3; 280ERROR HY000: Can't initialize function 'bson_object_grp'; This function requires 2 arguments (key, value) 281SELECT Bson_Object_Grp(NAME, SALARY) FROM t3; 282Bson_Object_Grp(NAME, SALARY) 283{"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} 284SELECT Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES") FROM t3 GROUP BY DEPARTMENT; 285Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES") 286{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.00,"SHORTSIGHT":5500.00}} 287{"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}} 288{"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}} 289{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.00,"ORELLY":13400.00,"BIGHORN":11000.00,"SMITH":9000.00,"CHERRY":4500.00}} 290SELECT Bson_Array_Grp(NAME) FROM t3; 291Bson_Array_Grp(NAME) 292["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"] 293SELECT Bson_Object_Key(name, title) FROM t3 WHERE DEPARTMENT = 318; 294Bson_Object_Key(name, title) 295{"BANCROFT":"SALESMAN"} 296{"MERCHANT":"SALESMAN"} 297{"SHRINKY":"ADMINISTRATOR"} 298{"WALTER":"ENGINEER"} 299{"TONGHO":"ENGINEER"} 300{"HONEY":"SECRETARY"} 301{"PLUMHEAD":"TYPIST"} 302{"WERTHER":"DIRECTOR"} 303{"WHEELFOR":"SALESMAN"} 304SELECT Bson_Object_Grp(name, title) FROM t3 WHERE DEPARTMENT = 318; 305Bson_Object_Grp(name, title) 306{"BANCROFT":"SALESMAN","MERCHANT":"SALESMAN","SHRINKY":"ADMINISTRATOR","WALTER":"ENGINEER","TONGHO":"ENGINEER","HONEY":"SECRETARY","PLUMHEAD":"TYPIST","WERTHER":"DIRECTOR","WHEELFOR":"SALESMAN"} 307# 308# Test value getting UDF's 309# 310SELECT BsonGet_String(Bson_Array_Grp(name),'[#]') FROM t3; 311BsonGet_String(Bson_Array_Grp(name),'[#]') 31227 313SELECT BsonGet_String(Bson_Array_Grp(name),'[","]') FROM t3; 314BsonGet_String(Bson_Array_Grp(name),'[","]') 315BANCROFT,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 316SELECT BsonGet_String(Bson_Array_Grp(name),'[>]') FROM t3; 317BsonGet_String(Bson_Array_Grp(name),'[>]') 318WHEELFOR 319SET @j1 = '[45,28,36,45,89]'; 320SELECT BsonGet_String(@j1,'1'); 321BsonGet_String(@j1,'1') 32228 323SELECT BsonGet_String(@j1 json_,'3'); 324BsonGet_String(@j1 json_,'3') 32545 326SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3'); 327BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3') 32845 329SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'["+"]') "list",'=' as "egal",BsonGet_String(Bson_Make_Array(45,28,36,45,89),'[+]') "sum"; 330list egal sum 33145+28+36+45+89 = 243 332SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0'); 333BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0') 33436 335SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*'); 336BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*') 337[36,45,89] 338SELECT BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc'); 339BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc') 340machin 341SET @j2 = '{"qty":56,"price":3.141600,"truc":"machin","garanty":null}'; 342SELECT BsonGet_String(@j2 json_,'truc'); 343BsonGet_String(@j2 json_,'truc') 344machin 345SELECT BsonGet_String(@j2,'truc'); 346BsonGet_String(@j2,'truc') 347machin 348SELECT BsonGet_String(@j2,'chose'); 349BsonGet_String(@j2,'chose') 350NULL 351SELECT BsonGet_String(NULL json_, NULL); 352BsonGet_String(NULL json_, NULL) 353NULL 354Warnings: 355Warning 1105 356/* NULL WARNING */ 357SELECT department, BsonGet_String(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department; 358department Sumsal 3590021 28500.00 3600318 72230.00 3610319 89800.95 3622452 45900.00 363SELECT BsonGet_Int(@j1, '4'); 364BsonGet_Int(@j1, '4') 36589 366SELECT BsonGet_Int(@j1, '[#]'); 367BsonGet_Int(@j1, '[#]') 3685 369SELECT BsonGet_Int(@j1, '[+]'); 370BsonGet_Int(@j1, '[+]') 371243 372SELECT BsonGet_Int(@j1 json_, '3'); 373BsonGet_Int(@j1 json_, '3') 37445 375SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3'); 376BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3') 37745 378SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]'); 379BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]') 38045 381SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]'); 382BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]') 383243 384SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0'); 385BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0') 38636 387SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1'); 388BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1') 38928 390SELECT BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty'); 391BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty') 39256 393SELECT BsonGet_Int(@j2 json_, 'price'); 394BsonGet_Int(@j2 json_, 'price') 3953 396SELECT BsonGet_Int(@j2, 'qty'); 397BsonGet_Int(@j2, 'qty') 39856 399SELECT BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); 400BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') 401NULL 402SELECT BsonGet_Int(BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)), '1.*'), '[+]') sum; 403sum 404170 405SELECT department, BsonGet_Int(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"), 'salaries.[+]') Sumsal FROM t3 GROUP BY department; 406department Sumsal 4070021 28500 4080318 72230 4090319 89800 4102452 45900 411SELECT BsonGet_Real(@j1, '2'); 412BsonGet_Real(@j1, '2') 41336.000000000000000 414SELECT BsonGet_Real(@j1 json_, '3', 2); 415BsonGet_Real(@j1 json_, '3', 2) 41645.00 417SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3'); 418BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3') 41945.000000000000000 420SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]'); 421BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]') 42245.000000000000000 423SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]'); 424BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]') 425243.000000000000000 426SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]'); 427BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]') 42848.600000000000000 429SELECT BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0'); 430BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0') 43136.000000000000000 432SELECT BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price'); 433BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price') 4343.141600000000000 435SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty'); 436BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty') 43756.000000000000000 438SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price'); 439BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price') 4403.141600000000000 441SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4); 442BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4) 4433.1416 444SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); 445BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') 446NULL 447SELECT department, BsonGet_Real(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department; 448department Sumsal 4490021 28500.000000000000000 4500318 72230.000000000000000 4510319 89800.950000000000000 4522452 45900.000000000000000 453# 454# Documentation examples 455# 456SELECT 457BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '4') "Rank", 458BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[#]') "Number", 459BsonGet_String(Bson_Make_Array(45,28,36,45,89), '[","]') "Concat", 460BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]') "Sum", 461BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]', 2) "Avg"; 462Rank Number Concat Sum Avg 46389 5 45,28,36,45,89 243 48.60 464SELECT 465BsonGet_String('{"qty":7,"price":29.50,"garanty":null}', 'price') "String", 466BsonGet_Int('{"qty":7,"price":29.50,"garanty":null}', 'price') "Int", 467BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price') "Real"; 468String Int Real 46929.50 29 29.500000000000000 470SELECT BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price', 3) "Real"; 471Real 47229.500 473# 474# Testing Locate 475# 476SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin'); 477BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin') 478$.truc 479SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56); 480BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56) 481$.qty 482SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416); 483BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416) 484$.price 485SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose'); 486BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose') 487NULL 488SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'Jack') Path; 489Path 490$.AUTHORS[1].FN 491SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'jack' ci) Path; 492Path 493$.AUTHORS[1].FN 494SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"Jack", "LN":"London"}' json_) Path; 495Path 496$.AUTHORS[1] 497SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"jack", "LN":"London"}' json_) Path; 498Path 499NULL 500SELECT BsonLocate('[45,28,36,45,89]',36); 501BsonLocate('[45,28,36,45,89]',36) 502$[2] 503SELECT BsonLocate('[45,28,36,45,89]' json_,28.0); 504BsonLocate('[45,28,36,45,89]' json_,28.0) 505NULL 506SELECT Bson_Locate_All('[45,28,36,45,89]',10); 507Bson_Locate_All('[45,28,36,45,89]',10) 508[] 509SELECT Bson_Locate_All('[45,28,36,45,89]',45); 510Bson_Locate_All('[45,28,36,45,89]',45) 511["$[0]","$[3]"] 512SELECT Bson_Locate_All('[[45,28],36,45,89]',45); 513Bson_Locate_All('[[45,28],36,45,89]',45) 514["$[0][0]","$[2]"] 515SELECT Bson_Locate_All('[[45,28,45],36,45,89]',45); 516Bson_Locate_All('[[45,28,45],36,45,89]',45) 517["$[0][0]","$[0][2]","$[2]"] 518SELECT Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]')); 519Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]')) 520["$[0][0]","$[0][2]","$[2]"] 521SELECT BsonLocate('[[45,28,45],36,45,89]',45,n) from t1; 522BsonLocate('[[45,28,45],36,45,89]',45,n) 523$[0][0] 524$[0][2] 525$[2] 526NULL 527NULL 528SELECT BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) FROM t1; 529BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) 530$[0][0] 531$[0][2] 532$[2] 533NULL 534NULL 535SELECT 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; 536Path 537$[0][0] 538$[0][2] 539$[2] 540SELECT Bson_Locate_All('[45,28,[36,45,89]]',45); 541Bson_Locate_All('[45,28,[36,45,89]]',45) 542["$[0]","$[2][1]"] 543SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0)); 544Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0)) 545[] 546SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0); 547Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0) 548["$[1][1]"] 549SELECT BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_); 550BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_) 551$[1] 552SELECT BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_); 553BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_) 554$[0] 555SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','45') "All paths"; 556All paths 557[] 558SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_); 559Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_) 560["$[1][0]"] 561SELECT BsonGet_Int(Bson_Locate_All('[[45,28],[[36,45],89]]',45), '[#]') "Nb of occurs"; 562Nb of occurs 5632 564SELECT Bson_Locate_All('[[45,28],[[36,45],89]]',45,2); 565Bson_Locate_All('[[45,28],[[36,45],89]]',45,2) 566["$[0][0]"] 567SELECT BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0'); 568BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0') 569$[0] 570SELECT BsonLocate(Bson_File('test/biblio.json'), 'Knab'); 571BsonLocate(Bson_File('test/biblio.json'), 'Knab') 572$[0].AUTHOR[1].LASTNAME 573SELECT Bson_Locate_All('test/biblio.json' jfile_, 'Knab'); 574Bson_Locate_All('test/biblio.json' jfile_, 'Knab') 575["$[0].AUTHOR[1].LASTNAME"] 576# 577# Testing json files 578# 579SELECT Bfile_Make('[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]}, 580{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]}, 581{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]}, 582{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}]', 'test/fx.json', 0) AS NewFile; 583NewFile 584test/fx.json 585SELECT Bfile_Make('test/fx.json', 1); 586Bfile_Make('test/fx.json', 1) 587test/fx.json 588SELECT Bfile_Make('test/fx.json' jfile_); 589Bfile_Make('test/fx.json' jfile_) 590test/fx.json 591SELECT Bfile_Make(Bbin_File('test/fx.json'), 0); 592Bfile_Make(Bbin_File('test/fx.json'), 0) 593test/fx.json 594SELECT Bson_File('test/fx.json', 1); 595Bson_File('test/fx.json', 1) 596[{"_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]}] 597Warnings: 598Warning 1105 File pretty format doesn't match the specified pretty value 599SELECT Bson_File('test/fx.json', 2); 600Bson_File('test/fx.json', 2) 601[{"_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]}] 602Warnings: 603Warning 1105 File pretty format doesn't match the specified pretty value 604SELECT Bson_File('test/fx.json', 0); 605Bson_File('test/fx.json', 0) 606[{"_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]}] 607SELECT Bson_File('test/fx.json', '0'); 608Bson_File('test/fx.json', '0') 609{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]} 610SELECT Bson_File('test/fx.json', '[?]'); 611Bson_File('test/fx.json', '[?]') 612NULL 613Warnings: 614Warning 1105 Invalid function specification ? 615SELECT BsonGet_String(Bson_File('test/fx.json'), '1.*'); 616BsonGet_String(Bson_File('test/fx.json'), '1.*') 617{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]} 618SELECT BsonGet_String(Bson_File('test/fx.json'), '1'); 619BsonGet_String(Bson_File('test/fx.json'), '1') 6206 car roadster 56000 (6, 9) 621SELECT BsonGet_Int(Bson_File('test/fx.json'), '1.mileage') AS Mileage; 622Mileage 62356000 624SELECT BsonGet_Real(Bson_File('test/fx.json'), '0.price', 2) AS Price; 625Price 6265.65 627SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings'); 628Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings') 629{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4,6]} 630SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings'); 631Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings') 632{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]} 633SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1); 634Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1) 635{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]} 636SELECT Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0); 637Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0) 638[6,2,4] 639SELECT Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1); 640Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1) 641{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2]} 642SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin); 643Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin) 644{"_id":7,"type":"food","item":"meat","origin":"france","ratings":[2,4]} 645SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size'); 646Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size') 647{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]} 648Warnings: 649Warning 1105 No sub-item at 'size' 650SELECT Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size'); 651Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size') 652{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":70},"ratings":[5,8,7]} 653SELECT Bson_Object_List(Bson_File('test/fx.json', '3.size')); 654Bson_Object_List(Bson_File('test/fx.json', '3.size')) 655["W","L","H"] 656# 657# Testing new functions 658# 659SELECT Bson_Item_Merge('["a","b","c"]','["d","e","f"]') as "Result"; 660Result 661["a","b","c","d","e","f"] 662SELECT Bson_Item_Merge(Bson_Make_Array('a','b','c'), Bson_Make_Array('d','e','f')) as "Result"; 663Result 664["a","b","c","d","e","f"] 665SELECT 666Bson_Set_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Set", 667Bson_Insert_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Insert", 668Bson_Update_Item(Bson_Make_Array(1,2,3,Bson_Object_Key('quatre',4)),'foo','$[1]',5,'$[3].cinq') "Update"; 669Set Insert Update 670[1,"foo",3,{"quatre":4,"cinq":5}] [1,2,3,{"quatre":4,"cinq":5}] [1,"foo",3,{"quatre":4}] 671SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux'); 672bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux') 673[1,3,{"quatre":4}] 674SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]'); 675bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]') 676[1,3,{"quatre":4}] 677SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux'); 678bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux') 679[1,2,3,{"quatre":4}] 680DROP TABLE t1; 681DROP TABLE t2; 682DROP TABLE t3; 683SELECT BsonSet_Grp_Size(10); 684BsonSet_Grp_Size(10) 68510 686