This topic describes the semi-structured encoding of JSON data supported by OceanBase AI Database.
Notice
The table-level parameter SEMISTRUCT_ENCODING_TYPE has been deprecated. Use SEMISTRUCT_PROPERTIES instead.
OceanBase AI Database supports enabling semi-structured encoding during table creation in user tenants. This is primarily controlled by the table-level parameter SEMISTRUCT_PROPERTIES. Additionally, you must set the table's ROW_FORMAT=COMPRESSED, otherwise an error will be reported.
Considerations
- When
SEMISTRUCT_PROPERTIES=(encoding_type=encoding)is specified, the table is considered a semi-structured table. In other words, semi-structured encoding is enabled for all JSON columns in the entire table. - When
SEMISTRUCT_PROPERTIES=(encoding_type=none)is specified, the table is considered a structured table. - You can also set the frequency threshold using the
freq_thresholdparameter. When semi-structured encoding is enabled, the system analyzes the frequency of occurrence for each path in the JSON data and stores paths with a frequency higher than the specified threshold as independent subcolumns. These subcolumns are called frequent columns. For example, if you have a user table where a JSON field stores user information, and 90% of users havenameandagefields, the system automatically extractsnameandageas independent frequent columns. Queries then directly access these columns without parsing the entire JSON, thereby improving query performance. For specific syntax and parameter descriptions, see CREATE TABLE. - The current
encoding_typeandfreq_thresholdare Online DDL syntax and do not support Offline DDL syntax.
Data formats
When JSON data is split into structured columns in a specific format, the columns derived from the JSON columns are called subcolumns. Subcolumns can be further split into different types, including sparse columns and frequent columns.
- Sparse columns: Subcolumns that are present in some JSON documents but absent in others, and whose frequency is below the threshold specified by the table-level parameter
freq_threshold. - Frequent columns: Subcolumns that appear more frequently in the JSON data than the threshold specified by the table-level parameter
freq_thresholdare stored as independent columns to improve query filtering performance.
For example:
{"id": 1001, "name": "n1", "nickname": "nn1"}
{"id": 1002, "name": "n2", "nickname": "nn2"}
{"id": 1003, "name": "n3", "nickname": "nn3"}
{"id": 1004, "name": "n4", "nickname": "nn4"}
{"id": 1005, "name": "n5"}
Among them, id and name are fields present in every JSON object, with a frequency of 100%. nickname is present in only four JSON objects, with a frequency of 80%.
If freq_threshold is defined as 100%, nickname will be inferred as a sparse column, and id and name will be inferred as frequent columns. If it is defined as 80%, nickname, id, and name will all be inferred as frequent columns.
Examples
Enable semi-structured encoding
Notice
If the semi-structured encoding feature is enabled, ensure that the cluster parameter micro_block_merge_verify_level is set to its default value
2. Do not disable microblock merge verification.Example of enabling a table during creationExample of modifying an existing table to enable semi-structured encodingCREATE TABLE t1( j json) ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES=(encoding_type=encoding, freq_threshold=50);For more information about syntax, see CREATE TABLE.
CREATE TABLE t1(j json); ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=encoding, freq_threshold=50);For more information about the syntax, see ALTER TABLE.
Some modification limitations:
- Modified column threshold. If semi-structured data is not enabled, no error is reported, but the setting does not take effect.
- The
freq_thresholdparameter cannot be modified during direct load or when a table is locked. - Modifying one sub-parameter does not affect the others.
Disable semi-structured encoding
When
SEMISTRUCT_PROPERTIESis set to(encoding_type=none), semi-structured encoding is disabled. This operation does not affect existing data but only takes effect on data written subsequently. The following example disables semi-structured encoding:ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=none);Query semi-structured encoding configurations
Query the semi-structured encoding configuration using the
SHOW CREATE TABLEstatement. Example statement:SHOW CREATE TABLE t1;The return result is as follows:
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `j` json DEFAULT NULL ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPRESSED COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 SEMISTRUCT_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=50) | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setThis parameter is displayed only when
SEMISTRUCT_PROPERTIES=(encoding_type=encoding)is specified, indicating that the semi-structured encoding feature is enabled.Using semi-structured encoding can improve the performance of conditional filtering queries using the JSON_VALUE. Based on JSON semi-structured encoding technology, OceanBase has optimized the performance of conditional filtering queries using
JSON_VALUEexpressions. Since JSON data has been split into subcolumns, the system can directly filter based on the encoded subcolumn data without restoring the complete JSON structure, significantly improving query efficiency.A sample query is as follows:
-- Query rows where the value of the name field is 'Devin'. SELECT * FROM t WHERE JSON_VALUE(j_doc, '$.name' RETURNING CHAR) = 'Devin';Consider the following character set considerations:
OceanBase AI Database uses
utf8_binencoding for JSON.To ensure proper white-box filtering of strings, it is recommended to set:
SET @@collation_server = 'utf8mb4_bin'; SET @@collation_connection='utf8mb4_bin';