A JSON value must consist of objects, arrays, strings, numbers, booleans (false/true), or null. Among these, false, true, and null are case-insensitive.
JSON Text Structure
The JSON text structure includes characters, strings, numbers, and three literal names. Various separators, including spaces, horizontal tabs, line breaks, and carriage returns, are allowed before or after any structural character.
start_array = [ left square bracket
Start object = { Left curly brace
End array = ] Right square bracket
Final object = } Right curly brace
Name separator = : colon
Value separator = , comma
Object
An object is represented as a pair of curly braces enclosing zero or more name/value pairs (or members). The names within an object must be unique. A name consists of a string, followed by a colon that separates the name from its value. Multiple names are separated by commas. Here is an example:
{ "NAME": "SAM", "Height": 175, "Weight": 100,"Registered" : false}
Arrays
An array is represented by square brackets enclosing zero or more values (also called elements). Array elements are separated by commas, and the values in the array do not need to be identical.
For example:
["abc", 10, null, true, false]
Numbers
Numbers are written in decimal notation, consisting of an integer part, optionally preceded by a minus sign (-), followed by a fractional part and/or an exponential part. Leading zeros are not allowed. The fractional part consists of one or more digits following a decimal point. The exponential part begins with the letter E (uppercase or lowercase), optionally followed by a plus sign (+) or a minus sign (-). After E and the optional sign, one or more digits can follow.
Here is an example:
[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]
String
A string must be enclosed in double quotation marks (""). All Unicode characters can be included in a string, except for those that require escape sequences (including quotation marks, backslashes, and control characters).
JSON text should be encoded in UTF-8, UTF-16, or UTF-32. The default encoding is UTF-8.
For example:
{"Url": "http://www.example.com/image/481989943"}
Create a JSON value
OceanBase AI Database supports the following DDL operations on the JSON data type:
Create a table with a JSON column.
Add or drop a JSON column.
Create an index on a column with the generated column type set to JSON.
Supports enabling semi-structured encoding when creating tables in user tenants.
You can enable semi-structured encoding for an existing table in a user tenant.
Limitations
You can create multiple columns of the JSON data type in each table, but there are the following limitations:
Columns of the JSON data type cannot be used as
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEY, but can have theNOT NULLorCHECKconstraint.A column of the JSON data type cannot have a default value.
Columns of the JSON data type cannot be used as partitioning keys.
The length of JSON data cannot exceed the length of
LONGTEXT, and the maximum depth of each JSON object or array is 99.
Examples
Create/Modify a JSON column
obclient> CREATE TABLE tbl1 (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 MODIFY docs JSON CHECK(docs <'{"a" : 100}');
Query OK, 0 rows affected
obclient> CREATE TABLE json_tab(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary key',
json_info JSON COMMENT 'JSON data',
json_id INT GENERATED ALWAYS AS (json_info -> '$.id') COMMENT 'Virtual field for JSON data',
json_name VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
index json_info_id_idx (json_id)
)COMMENT 'JSON example table';
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab ADD COLUMN json_info1 JSON;
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab ADD INDEX (json_name);
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab drop COLUMN json_info1;
Query OK, 0 rows affected
Create an index using a generated column as the specified key
obclient> CREATE TABLE jn ( c JSON, g INT GENERATED ALWAYS AS (c->"$.id"));
Query OK, 0 rows affected
obclient> CREATE INDEX idx1 ON jn(g);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
obclient> INSERT INTO jn (c) VALUES
('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name |
+-------+
| Fred |
| Wilma |
+-------+
2 rows in set
obclient> EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|jemp(idx1)|2 |92 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([JSON_UNQUOTE(JSON_EXTRACT(jemp.c, '$.name'))]), filter(nil),
access([jemp.c]), partitions(p0)
1 row in set
Use semi-structured encoding
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:
- When
SEMISTRUCT_PROPERTIES=(encoding_type=encoding)is specified, the table is considered a semi-structured table. This means all columns in the table will enable semi-structured encoding; - 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. For more information, see CREATE TABLE. - Currently, the
encoding_typeandfreq_thresholdparameters are supported in Online DDL syntax but not in Offline DDL syntax.
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 syntax, see ALTER TABLE.
Some modification limitations:
- Modified column threshold. If semi-structured data is not enabled, no error is reported, but the modified column threshold 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 by 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 with the JSON_VALUE. Based on JSON semi-structured encoding technology, OceanBase has optimized the performance of conditional filtering queries using JSON_VALUE expressions. Since JSON data is 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';
Considerations on character sets are as follows:
The JSON format in OceanBase AI Database uses
utf8_binencoding.To ensure proper white-box filtering of strings, it is recommended to set:
SET @@collation_server = 'utf8mb4_bin'; SET @@collation_connection='utf8mb4_bin';