Examples: Get the BSON Representation of JSON Data

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

In these examples, the column j1 contains JSON data in text format. The AsBSON method is called to get the BSON representation of this data with STRICT validation or with LAX validation.

Setting up the AsBSON Examples

CREATE TABLE jsonTable(id INTEGER, j1 JSON);

/*insert {"hello": "world"}*/
INSERT INTO jsonTable(1, '{"hello": "world"}');

Example: AsBSON with No Validation Clause

AsBSON is called without specifying STRICT or LAX. In this case, the method defaults to using LAX validation.

SELECT j1.AsBSON() FROM jsonTable;

Result:

j1.AsBSON ()
--------------------------------------------
160000000268656C6C6F0006000000776F726C640000

Example: AsBSON with STRICT Validation with Successful Result

SELECT j1.AsBSON('STRICT') FROM jsonTable;

Result:

j1.AsBSON ('STRICT')
--------------------------------------------
160000000268656C6C6F0006000000776F726C640000

Example: AsBSON with STRICT Validation with Failed Result

/*insert {"$hello": "world"}*/
INSERT INTO jsonTable(2, '{"$hello": "world"}');

SELECT j1.AsBSON('STRICT') FROM jsonTable;

Result:

*** Failure 7548 Dollar sign ('$') not permitted in a key of a BSON
document, according to the chosen validation scheme. 
Error encountered at offset 4.

Example: AsBSON with LAX Validation

With LAX validation, the second row inserted into the table is not flagged as incorrect syntax.

SELECT j1.AsBSON('LAX') FROM jsonTable;

Result:

j1.AsBSON ('LAX')
--------------------------------------------
160000000268656C6C6F0006000000776F726C640000
17000000022468656C6C6F0006000000776F726C640000