genBSON function

The genBSON function provides an efficient method for copying data from an Informix® database into a JSON or BSON document store format.

The genBSON function copies data from a database table and converts it to BSON format, based on the column name and the column data type. The column name becomes the field name. The SQL column type indicates which BSON type is used. The SQL data value becomes the field value.

Syntax

GENBSON function

BSONGENBSON (rowtype [ , { 0 | keep_nulls } [ , { skip_id | 0 } ] ] )

Element Description Restrictions Syntax
keep_nulls Indicates whether to convert NULL values of SQL into a field-value pair (1) or to omit (0) the BSON field-value pair if the SQL value is NULL. Default is (0) no NULL field-value pairs. Only 0 or 1 is valid. Literal Number or any SQL expression that evaluates to 0 or1
rowtype Describes the columns to be converted Restricted to the supported SQL data types listed below Literal Row
skip_id By default, (0) creates a field that is named _id where the associated value is an ObjectId. Setting this value to (1) omits the _id field-value pair. Only 0 or 1 is valid. Literal Number

Usage

The genBSON function can convert most built-in data types of SQL to BSON field values. The following tables summarize the data type mapping that genBSON performs on built-in data types of SQL.

Table 1. genBSON conversion of SQL character and interval data types into BSON/JSON document store types.
SQL type BSON/JSON type
CHAR String
LVARCHAR String
NCHAR String
NVARCHAR String
SMALLFLOAT String
INTERVAL String
Table 2. genBSON conversion of SQL number, complex, date, and datetime data types into BSON/JSON document store types.
SQL type BSON/JSON type
SMALLINT Integer
INT Integer
BIGINT Long
BIGSERIAL Long
INT8 Long
SERIAL8 Long
MONEY Double
DECIMAL Double
FLOAT Double
SMALLFLOAT Double
BSON BSON - sub document
ROW type Sub document
COLLECTION Array
DATE Date - MongoDB specific type
DATETIME Date - MongoDB specific type

Built-in large object data types and other SQL types that are not listed are not supported as input to the genBSON function.

Example: Convert a table

The following example selects all columns of a table and converts them to a JSON document.

This genBSON function uses the built-in row type that exists for all tables. This example selects from the systables system catalog entries for the table, and asks genBSON to convert all columns. By accepting all defaults, any column with NULL values is omitted. The _id field is added to the document:

SELECT FIRST 1 genbson( systables )::JSON FROM systables;

{"_id":ObjectId("5319412b64d5b83f00000003"),
"tabname":"systables",
"owner":"informix",
"partnum":1048580,
"tabid":1,
"rowsize":500,
"ncols":26,
"nindexes":2,
"nrows":266.000000,
"created":41702,
"version":65539,
"tabtype":"T",
"locklevel":"R",
"npused":23.000000,
"fextsize":16,
"nextsize":16,
"flags":0,
"type_xid":0,
"am_id":0,
"pagesize":2048,
"ustlowts":ISODate("2014-03-05T14:46:00.000Z"),
"secpolicyid":0,
"protgranularity":"",
"statlevel":""}

Example: Convert specific columns

The following example illustrates how to select a subset of columns in a table, and convert them to a JSON document.

This statement creates a row type that contains the tabname and tabid columns from the system catalog table systables, and creates the _id field:

SELECT FIRST 1 genbson( ROW(tabname, tabid))::JSON FROM systables;

{"_id":ObjectId("5319414764d5b83f00000005"),"tabname":"systables","tabid":1}
The following similar statement creates a row type that contains the tabname, tabid, and site columns from the system catalog table systables, allows SQL NULL columns, and omits the addition of the _id field:
SELECT FIRST 1 genbson( ROW(tabname, tabid, site), 1, 1)::JSON 
FROM systables;

(expression)  {"tabname":"systables","tabid":1,"site":null}

Example: Convert multiple data types

Here is a simple example to show the supported data types and how the genBSON function converts them to BSON.

The following statements create and populate the mytypes table, which has columns of different data types:

CREATE TABLE mytypes
(
c_char       char(20),
c_varchar    varchar(92),
c_null       char(200),
c_varchar1   varchar(92,10),
c_smallint   smallint,
c_int        integer,
c_serial     serial,
c_date       date,
c_bigint     bigint,
c_bigserial  bigserial,
c_float      float,
c_smallfloat smallfloat,
c_decimal    decimal(10,4),
c_int8       int8,
c_lvarchar   lvarchar,
c_bson       BSON,
c_coll_int   SET(INTEGER NOT NULL),
c_uname_row  ROW( fname CHAR(10), lname VARCHAR(20), age INTEGER),
c_datetime   DATETIME YEAR TO FRACTION(5),
c_interval   INTERVAL DAY(3) TO DAY
);

INSERT INTO mytypes
  VALUES
(
"john",
"Tim",
NULL,
"Scott",
27,
200000,
0,
"1/1/2014",
50000000000,
0,
123.123,
2468.2468,
12345.6789,
1234567890,
"long text data",
"{ key: 27 }"::JSON,
"SET{ 1, 2, 3, 4, 5, 6, 7 }",
ROW("JOHN","MILL", 7 ),
'2007-7-27 10:12:11',
INTERVAL (16) DAY TO DAY
);

The following statement returns the contents of the mytypes table with the value of the c_bson column in the c_bson2 column:

SELECT FIRST 1 *,c_bson::JSON  AS c_bson2 FROM mytypes;
c_char          john
c_varchar       Tim
c_null          
c_varchar1      Scott
c_smallint      27
c_int           200000
c_serial        1
c_date          01/01/2014
c_bigint        50000000000
c_bigserial     1
c_float         123.1230000000
c_smallfloat    2468.246830000
c_decimal       12345.6789
c_int8          1234567890
c_lvarchar      long text data 
c_bson                        
c_coll_int      SET{1    ,2      ,3      ,4      ,5      ,6      ,7     } 
c_uname_row     ROW('JOHN','MILL',7          ) 
c_datetime      2007-07-27 10:12:11.00000
c_interval        16
c_bson2         {"key":27} 

1 row(s) retrieved.

The following statement returns the contents of the mytypes table as a JSON document:


SELECT FIRST 1 genbson( mytypes )::JSON FROM mytypes;

{"_id":ObjectId("5319922755d0a64400000000"),
"c_char":"john",
"c_varchar":"Tim",
"c_varchar1":"Scott",
"c_smallint":27,
"c_int":200000,
"c_serial":1,
"c_date":ISODate("2014-01-01T00:00:00.000Z"),
"c_bigint":50000000000,
"c_bigserial":1,
"c_float":123.123000,
"c_smallfloat":2468.246826,
"c_decimal":12345.678900,
"c_int8":1234567890,
"c_lvarchar":"long text data",
"c_bson":{"key":27},
"c_coll_int":[1,2,3,4,5,6,7],
"c_uname_row":{"fname":"JOHN",
"lname":"MILL","age":7},
"c_datetime":ISODate("2007-07-27T10:12:11.000Z"),
"c_interval":"16"}

1 row(s) retrieved.

Example: Copy a table into a BSON column

The following example illustrates how to copy the contents of the mytable table into the BSON column of the mybson table:

CREATE TABLE mybson ( c1 serial, c2 bson);
INSERT INTO mybson SELECT 0, genbson( mytypes ) FROM mytypes;
SELECT c1, c2::JSON  FROM mybson;