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