![Using JSON/BSON types 5 BSON_GET function – retrieve key-value pair from the BSON column select bson_get(data, "name")::json from tab3; (expression) {"name":"informix"} BSON Index – create index on a field within BSON column - cannot create index on entire BSON column - use BSON_GET() and “USING BSON” keywords as the index-key specification create index idx on tab3(bson_get(data, "name")) using bson; create index idx1 on tab3(bson_get(data, "name"), bson_get(data, "ver"))using bson; Nested document and Array example : {"person":{"givenname":"Jim","surname":"Flynn","age":29,"cars":[" dodge","olds"]}} select bson_get(data, "name", "title")::json from tab3; (expression) {"title":"informix"}](https://image.slidesharecdn.com/b02-json-bson-151222230241/75/Using-JSON-BSON-types-in-your-hybrid-application-environment-5-2048.jpg)
![Using JSON/BSON types 8 Simple expressions (dotted notation) BSON column - Access key-value pair by using dot notation {"person":{"givenname":"Jim","surname":"Flynn","age":29,"cars":["dodge","olds"]}} select data.person.surname::json from tab3; (expression) {"surname":"Flynn"} select data.person.cars::json from tab3; (expression) {"cars":["dodge","olds"]} bson_size() - return size for specific field or entire bson document select bson_size(data,""), bson_size(data, "name") from tab3; (expression) (expression) 41 13](https://image.slidesharecdn.com/b02-json-bson-151222230241/75/Using-JSON-BSON-types-in-your-hybrid-application-environment-8-2048.jpg)
![SQL features with JSON/BSON BSON array examples create table arr_tab(id int, c bson); insert into arr_tab values(1, '{objarr:[1,2,3]}'::json); insert into arr_tab values(2, '{objarr:[1,2,[4,5],3]}'::json); insert into arr_tab values(3, '{objarr:[1,2,{mykey:[4,5]},3]}'::json); insert into arr_tab values(5, '{objarr:[1,2,[4,5],3, ["foo", "bar", 4]]}'::json); insert into arr_tab values(7, '{objarr:[1,2,[4,5],3, ["foo", [9,10], "bar", 4]]}'::json); BSON array of documents insert into tab1 values('{user:{fn:"Bob", children:[{fn:"Jack", ln:"Smith"},{fn:"Jane", ln:"Smith"}], ln:"Smith", age:50}, zip:95032, devices: ["laptop","desktop","tablet"]}'::json); select bson_get(c, "user.children.0")::json from tab1; (expression) {"children":{"fn":"Jack","ln":"Smith"}}](https://image.slidesharecdn.com/b02-json-bson-151222230241/75/Using-JSON-BSON-types-in-your-hybrid-application-environment-16-2048.jpg)
![SQL features with JSON/BSON BSON value functions on array elements {"name":"informix","ver":12} {"name":["ifmx","informix"],"ver":[10,11]} ← “name” & “ver” are array elements {"database":"informix","number":10} select bson_value_varchar(data, "name"), bson_value_int(data, "ver") from tab3; (expression) informix (expression) 12 (expression) ifmx (expression) 10 (expression) (expression)](https://image.slidesharecdn.com/b02-json-bson-151222230241/75/Using-JSON-BSON-types-in-your-hybrid-application-environment-17-2048.jpg)
Using JSON/BSON types in your hybrid application environment
- 1. Using JSON/BSON typesin your hybrid application environment Ajaykumar Gupte IBM 1
- 2. Agenda 2 Overview Using JSON/BSON types SQLfeautues with JSON/BSON Performance / tips
- 3. Overview 3 JSON type – Storesingle or multiple documents in a row – string, numeric, boolean, date, timestamp – useful when storing and retrieving entire document BSON type – Binary representation of JSON – built in functions based on BSON type – casting between JSON/BSON – useful when processing sub elements of JSON document JSON array - based on simple types, json document itself or nested array Nested JSON – simple to multiple JSON documents in a single row
- 4. Using JSON/BSON types 4 Supportfor multiple JSON/BSON columns in a single table – create table tab1 ( c1 json , c2 bson , c3 json , c4 bson); Storage – size < 4 KB document are stored in-row Size > 4 KB are stored in sbpace assigned for the table Load JSON documents – use external table to load bson documents – server can insert and display data only in JSON type create table tab2 ( data json); load from 'd.unl' insert into tab2; insert into tab2 values ('{"name":"informix", "ver":11}'); Insert statement - create table tab3 ( data bson); – To insert plain text with an SQL statement into a BSON column, cast to JSON insert into tab3 values ('{"name":"informix", "ver":11}'::json); insert into tab3 select * from tab2; {"name":"informix", "ver":12}| {"name":"informix", "ver":11}|
- 5. Using JSON/BSON types 5 BSON_GETfunction – retrieve key-value pair from the BSON column select bson_get(data, "name")::json from tab3; (expression) {"name":"informix"} BSON Index – create index on a field within BSON column - cannot create index on entire BSON column - use BSON_GET() and “USING BSON” keywords as the index-key specification create index idx on tab3(bson_get(data, "name")) using bson; create index idx1 on tab3(bson_get(data, "name"), bson_get(data, "ver"))using bson; Nested document and Array example : {"person":{"givenname":"Jim","surname":"Flynn","age":29,"cars":[" dodge","olds"]}} select bson_get(data, "name", "title")::json from tab3; (expression) {"title":"informix"}
- 6. Using JSON/BSON types 6 Functionalindex using BSON_GET create function f_acct1(acct_id INTEGER, branch_name VARCHAR(20), acct_addr LVARCHAR(4041)) returns int with (not variant); return acct_id * acct_id; end function; create index acct_ixa on account(f_acct1(acct_id,"DBSERVER","A234567890123456789")) using bson;
- 7. Using JSON/BSON types 7 Casting– JSON to BSON – all processing functions are based on BSON BSON to JSON – to display output results from key-value pair or entire document - to view data in readable format select data::json from tab3; select data::json from tab3 where bson_get(data, "ver") = '{"ver":11}'; (U0001) - bson_input: bson_from_char failed! Error in line 1 Near character position 72 select data::json from tab3 where bson_get(data, "ver") = '{"ver":11}'::json; (expression) {"name":"informix","ver":11} select data::json from tab3 where bson_get(data, "ver") > '{ "ver":11}'::json; (expression) {"name":"informix","ver":12}
- 8. Using JSON/BSON types 8 Simpleexpressions (dotted notation) BSON column - Access key-value pair by using dot notation {"person":{"givenname":"Jim","surname":"Flynn","age":29,"cars":["dodge","olds"]}} select data.person.surname::json from tab3; (expression) {"surname":"Flynn"} select data.person.cars::json from tab3; (expression) {"cars":["dodge","olds"]} bson_size() - return size for specific field or entire bson document select bson_size(data,""), bson_size(data, "name") from tab3; (expression) (expression) 41 13
- 9. BSON functions 9 – Built-infunctions to manipulate BSON data – Useful to extract values based on defined types – can be used in projection list, where clause expressions – These functions can be used in create index statement BSON_VALUE_INT( ) - return or operate on numeric data on BSON column select bson_value_int(data, "ver") from tab3; (expression) 11 12 select bson_value_int(data, "ver") from tab3 where bson_value_int(data, "ver") < 12; (expression) 11 – similar functions BSON_VALUE_BIGINT( ) , BSON_VALUE_DOUBLE( ) , BSON_VALUE_FLOAT( )
- 10. BSON functions 10 BSON_VALUE_BOOLEAN( )- returns string values 't' or 'f' – opaque BOOLEAN data type {"name":"informix", "ver":11, "state":true} select bson_value_boolean(data,"state") from tab3; (expression) t BSON_VALUE_DATE( ) - function returns integer string values for DATE data type '{"mydate" : ISODate("2013-07-02T16:39:22.343")}' select data::json from tab1 where bson_value_date(data, "mydate")::date = "07-02-2013"; (expression) {"mydate":ISODate("2013-07-02T16:39:22.343Z")} create index ix1 on tab1(bson_value_date(data,"mydate")) using bson;
- 11. BSON functions 11 BSON_VALUE_TIMESTAMP( )- returns or operate on built-in DATETIME data type select bson_value_date(data,"ts") from tab1; (expression) 2013-05-18 00:33:14.00000 BSON_VALUE_LVARCHAR( ) - converts a character string that is fewer than 32740 bytes in a BSON field to an LVARCHAR data type select data::json from tab1 where bson_value_lvarchar(data, "firstname") = "Peter"; Index case - create index ixfn on tab1 (bson_value_lvarchar(data,"firstname")) using bson; – similar function BSON_VALUE_VARCHAR( ) BSON_VALUE_OBJECTID( ) - returns the ObjectId values in a BSON document as strings SELECT BSON_VALUE_OBJECTID(data,"_id") FROM products; (expression) 54befb9eedbc233cd3a4b2fb
- 12. BSON functions BSON_UPDATE() -updates the contents of a BSON column with MongoDB update operators - update operators : $set, $unset, $inc {"name":"informix","ver":12,"state":true} update tab3 set data = bson_update(data, '{"$set":{"ver":11}}'); {"name":"informix","ver":11,"state":true} update tab3 set data = bson_update(data, '{"$inc":{"ver":1}}'); /*increment with 1 */ {"name":"informix","ver":12,"state":true} update tab3 set data = bson_update(data, '{"$unset":{"ver":12}}'); /* remove ver=12 */ (expression) {"name":"informix","state":true} insert into tab4 select bson_update(data, '{"$set":{"ver":10}}') from tab3; select data::json from tab4; (expression) {"name":"informix","ver":10,"state":true}
- 13. BSON functions Genbson() -copy data from database table into BSON format - useful to produce bson document form query result set - easy to copy table data into bson column SELECT FIRST 1 genbson( systables )::JSON FROM systables; SELECT FIRST 1 genbson( ROW(tabname, tabid))::JSON FROM systables; {"_id":ObjectId("5319414764d5b83f00000005"),"tabname":"systables","tabid":1} CREATE TABLE mybson ( c1 serial, c2 bson); INSERT INTO mybson SELECT 0, genbson( mytypes ) FROM mytypes; SELECT c1, c2::JSON FROM mybson;
- 14. SQL features withJSON/BSON String type casting - expressions with string type columns & BSON column select c1, data::json from tab4; c1 {"name":"informix","ver":11,"state":true} (expression) {"name":"informix","ver":11,"state":true} select c1 from tab4 where data::json::lvarchar = c1; c1 {"name":"informix","ver":11,"state":true} select c1, data::json from tab4; c1 informix (expression) {"name":"informix","ver":11,"state":true} select c1 from tab4 where data::json::lvarchar matches '*informix*'; c1 informix select c1 from tab4 where bson_value_lvarchar(data, "name") = c1; c1 informix data.name::varchar
- 15. SQL features withJSON/BSON BSON column sorting on single or multiple BSON document keys select data::json from tab3 order by bson_get(data, "ver") desc ; select data::json from tab3 order by data.ver::json desc; select data::json from tab3 order by bson_value_int(data, "ver") desc; (expression) {"name":"informix","ver":12} (expression) {"name":"informix","ver":11} JSON column sorting on entire document – cannot access key-value pair for sorting create table tab5(data json); select data from tab5 order by 1 desc; data {"name":"informix","ver":12} data {"name":"informix","ver":11}
- 16. SQL features withJSON/BSON BSON array examples create table arr_tab(id int, c bson); insert into arr_tab values(1, '{objarr:[1,2,3]}'::json); insert into arr_tab values(2, '{objarr:[1,2,[4,5],3]}'::json); insert into arr_tab values(3, '{objarr:[1,2,{mykey:[4,5]},3]}'::json); insert into arr_tab values(5, '{objarr:[1,2,[4,5],3, ["foo", "bar", 4]]}'::json); insert into arr_tab values(7, '{objarr:[1,2,[4,5],3, ["foo", [9,10], "bar", 4]]}'::json); BSON array of documents insert into tab1 values('{user:{fn:"Bob", children:[{fn:"Jack", ln:"Smith"},{fn:"Jane", ln:"Smith"}], ln:"Smith", age:50}, zip:95032, devices: ["laptop","desktop","tablet"]}'::json); select bson_get(c, "user.children.0")::json from tab1; (expression) {"children":{"fn":"Jack","ln":"Smith"}}
- 17. SQL features withJSON/BSON BSON value functions on array elements {"name":"informix","ver":12} {"name":["ifmx","informix"],"ver":[10,11]} ← “name” & “ver” are array elements {"database":"informix","number":10} select bson_value_varchar(data, "name"), bson_value_int(data, "ver") from tab3; (expression) informix (expression) 12 (expression) ifmx (expression) 10 (expression) (expression)
- 18. SQL features withJSON/BSON Distinct type for JSON/BSON columns create table tdist (c1 int, c2 float, c3 lvarchar(100), c4 json, c5 json, c6 json); create distinct type J AS json; create distinct type B as bson; – inserting data as J type insert into tdist (c1, c2, c3, c4, c5, c6) values (1, 2.4, "andre", '{name:"andre"}'::J,'{job:"testing"}'::J, '{location:"lenexa"}'::J); – using distinct types for casting select bson_get(c6, "location")::json from tdist where bson_get(c6, "location") = '{"location":"lenexa"}'::J::B; (expression) {"location":"lenexa"}
- 19. SQL features withJSON/BSON Temp table using JSON/BSON columns create temp table tempt (c1 int, c2 float, c3 lvarchar(100), c4 json, c5 json, c6 json); insert into tempt (c1, c2, c3, c4, c5, c6) values (323, 09.32432, "ame", '{name:"ame"}'::json, '{job:"db"}'::json, '{location:"miami"}'::json); select genbson(tempt)::json from tempt; (expression) {"_id":ObjectId("5525a329608cf75000000009"),"c1":323,"c2":9.32432 ,"c3":"ame","c4":"{name:"ame"}","c5":"{job:"db"}","c6":"{location:"miami"}"}
- 20. SQL features withJSON/BSON Explicit cast for JSON/LVARCAHR columns --cast function create function json_to_lvarchar2(inp json) returns lvarchar return cast(inp as lvarchar); end function; create function lvarchar_to_json2(inp lvarchar) returns json return cast(inp as json);; end function; create explicit cast if not exists(json as lvarchar with json_to_lvarchar2); create explicit cast if not exists(lvarchar as json with lvarchar_to_json2); create table tcast (c1 int, c2 float, c3 json, c4 lvarchar); insert into tcast (c1, c2, c3, c4) values (1, 0.23242, '{n:"andre"}'::json, '{n:"andre"}'::lvarchar); select c3::json from tcast where cast(c3 as lvarchar)=c4; select c3::json from tcast where c3=cast (c4 as json); (expression) {n:"andre"}
- 21. SQL features withJSON/BSON BSON column- views/derived tables - using view/derived table with BSON value functions to extract element values {"name":"informix","ver":12} {"name":"informix","ver":11} {"database":"informix","number":7} ← “ver” does not exists create view v1(vc1,vc2) as select bson_value_varchar(data, "name"), bson_value_int(data, "ver") from tab3; select * from v1 ; select * from ( select bson_value_varchar(data, "name"), bson_value_int(data, "ver") from tab3) dtab(vc1,vc2); vc1 informix vc2 12 vc1 informix vc2 11 vc1 vc2 data.name::varchar , data.ver::int
- 22. SQL features withJSON/BSON BSON column – SPL create table t3(id int, c1 bson, c2 lvarchar); create function func_bson(key1 lvarchar, value1 lvarchar) returns bson define bob1 bson; define bob2 lvarchar; let bob2 = '{"'||key1||'":"'||value1||'"}'; let bob1 = bob2::json::bson; insert into t3 values(30,bob1,bob2); return bob1; end function; execute function func_bson("key1","test_bson"); select c1::json, c2 from t3; (expression) {"key1":"test_bson"} c2 {"key1":"test_bson"}
- 23. SQL features withJSON/BSON BSON column – Triggers create table studentsInfo(table_id int,data bson); create table studentsGrade(table_id int, data bson); create procedure proc1() referencing NEW as n for studentsInfo; define b1 bson; SELECT bson_get(data, "name") into b1 FROM studentsInfo; insert into studentsGrade values (n.table_id, b1); end procedure; create trigger student_grade_trig insert on studentsInfo for each row (execute procedure proc1() with trigger references);
- 24. SQL features withJSON/BSON BSON column- Merge statement create table t1 (id int, name lvarchar(10), bcol1 bson, bcol2 bson); create table t2 (id int, name lvarchar(10), bcol1 bson, bcol2 bson); create synonym bsont1 for t1; create synonym bsont2 for t2; – merge t1 and t2 & update t2.bcol2 if bcol1 columns are matched merge into bsont2 using bsont1 on bsont2.bcol1=bsont1.bcol1 when matched then update set bsont2.bcol2=bsont1.bcol2;
- 25. SQL features withJSON/BSON BSON column in distributed query select id, data::json from rdb@$SERVER2:rt1 where bson_value_lvarchar(data,'address.address1.street') = 'third'; – loading data from remote server insert into lt2(id, data, len) select id, data, len from rdb@$SERVER2:rt2; insert into rdb@$SERVER2:rt2 select id, data, len from lt2; - remote spl execution select id, proc1(data), len from rdb@$SERVER2:rt2;
- 26. SQL features withJSON/BSON Sqexplain – using dot notation and json casting select bson_get(bcol1, "name")::json, bson_get(bcol2, "name")::json from t1, t2 where t1.bcol1.ver::json = t2.bcol2.ver::json 1) informix.t1: SEQUENTIAL SCAN 2) informix.t2: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.equal(informix.jsonoutput(BSON_GET (informix.t1.bcol1 , 'ver' ) ),informix.jsonoutput(BSON_GET (informix.t2.bcol2 , 'ver' ) )) UDRs in query: UDR id : 487 UDR name: equal UDR id : 504 UDR name: jsonoutput UDR id : 504 UDR name: jsonoutput UDR id : 504 UDR name: jsonoutput UDR id : 504 UDR name: jsonoutput
- 27. SQL features withJSON/BSON Sqexplain – using bson value function select bson_get(bcol1, "name")::json, bson_get(bcol2, "name")::json from t1, t2 where bson_value_int(bcol1, "ver")= bson_value_int(bcol2, "ver") 1) informix.t1: SEQUENTIAL SCAN 2) informix.t2: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: BSON_VALUE_INT (informix.t1.bcol1 , 'ver' ) = BSON_VALUE_INT (informix.t2.bcol2 , 'ver' ) UDRs in query: UDR id : 504 UDR name: jsonoutput UDR id : 504 UDR name: jsonoutput
- 28. SQL features withJSON/BSON Syscolumns information - create table tab1( data bson); colname data tabid 102 colno 1 coltype 40 collength 4096 colmin colmax extended_id 26 ← 25 for JSON data type Seclabelid 0 colattr 0
- 29. SQL features withJSON/BSON 29 JSON validation cases insert into tab3 values ('{"name":"informix", "ver":11}}'::json); (U0001) - JSON Syntax error at character 29 Error in line 1 Near character position 63 insert into tab3 values ('{"name":"informix", "ver":{}'::json); (U0001) - JSON Syntax error at character 29 Error in line 1 Near character position 61 insert into tab3 values ('{error:{"name":"informix", "ver":11}'::json); (U0001) - JSON Syntax error at character 37 Error in line 1 Near character position 69
- 30. SQL features withJSON/BSON 30 NULL/Empty BSON elements (expression) {"name":"informix","ver":12} (expression) {"name":"informix","ver":11} (expression) {"name":"informix","ver":{}} ← Empty BSON document (expression) ← NULL BSON ( insert into tab3 values(NULL)) select count(*) from tab3 where data is null; (count(*)) 1 select data::json from tab3 where bson_get(data, "ver") = '{"ver":{}}'::json; (expression) {"name":"informix","ver":{}} select bson_get(data, "test")::json from tab3; (expression) {} (expression) {} (expression) {} (expression) {}
- 31. Performance / tips 31 –up to 4 KB size of BSON document is stored in-row – monitor sbspaces (system & Temp) for large document processing – BSON value functions are based on BSON data type – using views & bson value functions, map bson element to relational table columns – select required bson keys using view/derived tables – maximum 7 BSON/JSON columns per table – BSON value functions – monitor and handle logic to detect array element & nested document in JSON document
- 32.