- Text (LATIN or UNICODE)
- Binary JSON (BSON)
- Universal Binary JSON (UBJSON)
The default storage format is text in the character set specified (or the default character set for the user).
- As an attribute of a table column
- As part of a JSON data type declaration in a CAST expression
- As an optional parameter to the JSON constructor
You can use HELP COLUMN to display the storage format of a JSON column.
You cannot define a JSON type with both a STORAGE FORMAT clause and a CHARACTER SET clause. For JSON data stored in a binary format, the character set for this data when it is exported as text is UNICODE. Therefore, no CHARACTER SET clause is needed for these formats.
Storage Format Comparison
BSON and UBJSON generally provide better traverse time as compared with text-based JSON. Data stored in either of these formats can be searched using extraction techniques, and you may see an improvement in retrieval time when these formats are used.
The following provides a comparison between the different storage formats as a guideline for selecting the best storage format for your data.
JSON Text Format
- Requires the least amount of time for insertion
- Slower retrieval time
- Potentially requires more space
BSON Format
- Validation is always done implicitly when converting from text
- Requires the most time to insert
- Faster retrieval time (tied with UBJSON)
- Works well when exchanging data with MongoDB
- Potential space savings when compared with text format
- Validation is always done implicitly when converting from text
- Requires more time to insert than the text format, but less than BSON
- Faster retrieval time (tied with BSON)
- Almost always provides space savings, especially when many numeric values are included in the data