JSON data is automatically validated by default. You can use the DBS Control DisableJSONValidation field or the SET SESSION JSON IGNORE ERRORS attribute to disable validation if the data is from a reliable source and is accurate. Disabling validation may improve performance.
The DBS Control DisableJSONValidation field controls whether validation is enabled or disabled on a system-wide level. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .
To disable validation for the current session, use the following statement:
SET SESSION JSON IGNORE ERRORS ON;
To re-enable validation for the current session, use the following statement:
SET SESSION JSON IGNORE ERRORS OFF;
- Even with validation disabled, JSON text imported to the database and stored as JSON text is minimally validated to ensure that the opening and closing braces and brackets of the JSON document properly match. For example, '{' matches with '}' and '[' matches with ']'. Failure to pass this minimal validation will result in an error.
- JSON text imported to the database and stored as one of the binary storage formats is still validated. Vantage does not allow an improper JSON document to be converted to one of the binary storage formats. In this case, the settings for the JSON IGNORE ERRORS session attribute or the DBS Control DisableJSONValidation field have no impact.
- The maximum length specified for a JSON column that uses a binary storage format must cover the length of the data in its binary format. If validation is disabled and the binary data inserted exceeds the defined length of the column, an error is reported.
If the binary data inserted fits within the defined length of the column, but the serialized (text) format exceeds the length, an error is reported when trying to access the data as text.
- JSON data imported to the database in its BSON format and stored in the BSON format is accepted without any validation. Because validation is completely disabled, no errors are reported for any issues encountered.
- JSON data cannot be imported to the database in UBJSON format; therefore, the UBJSON data is always validated when it is converted from its source format because Vantage cannot convert invalid data.
- A user document element name cannot begin with a '$' character.
- A user document element name cannot have a '.' character in the name.
- The element name _id is reserved for use as a primary key id, but you can store anything that is unique in that field.
Even with validation enabled, this level of validation needed for MongoDB is not performed when inserting data into a JSON column which stores its data as BSON. However, the AsBSON method provides an optional parameter that allows you to perform this strict validation.
You can use the BSON_CHECK function to verify that JSON data in BSON format is compliant with BSON syntax. If the data is valid, the BSON_CHECK function returns OK. The BSON_CHECK function also has an optional parameter that allows you to specify whether or not to perform a strict validation which verifies that the BSON syntax is valid for MongoDB.
You can use the JSON_CHECK function to verify that a JSON string is compliant with JSON syntax. If the JSON string is valid, the JSON_CHECK function returns OK.