genBSON function

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

>>-BSON--GENBSON------------------------------------------------>

>--(--rowtype--+---------------------------------------+--)----><
               |    .-0----------.                     |      
               '-,--+-keep_nulls-+--+----------------+-'      
                                    |    .-skip_id-. |        
                                    '-,--+-0-------+-'        

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.

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} 

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;