AsBSON JSON Method Examples | Teradata Vantage - Examples: Get the BSON Representation of JSON Data - Advanced SQL Engine - Teradata Database

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