Create a JSON value

A JSON value must be an object (JSON object), array, string, number, boolean (false or true), or null. The values false, true, and null must be in lowercase.

JSON text structure

The JSON text structure includes characters, strings, numbers, and three literal names. Any structure character can be preceded or followed by various whitespace characters, including spaces, horizontal tabs, line breaks, and carriage returns.

       Start array =  [ Left bracket

       Start object =  { Left brace

       End array =  ] Right bracket

       End object =  } Right brace

       Name separator = : Colon

       Value separator = , Comma

Objects

An object is a collection of zero or more name-value pairs (also called members) enclosed in a pair of braces. The names in an object must be unique. A name is a string, and each name is followed by a colon that separates the name from the value. A single comma separates multiple name-value pairs. Here is an example:

{ "NAME": "SAM",  "Height": 175, "Weight":  100, "Registered" : false}

Arrays

An array is a collection of zero or more values (also called elements) enclosed in a pair of brackets. Array elements are separated by commas, and the values in an array do not need to be the same.

Here is an example:

["abc", 10, null, true, false]

Numbers

A number is a decimal number that has an integer component, which may be prefixed by an optional minus sign (-), followed by a fractional component and/or an exponent component. Leading zeros are not allowed. The fractional component is a decimal point followed by one or more digits. The exponent component begins with an uppercase or lowercase E, followed by an optional plus sign (+) or minus sign (-). After the E and the optional sign, one or more digits follow.

Here is an example:

[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]

Strings

A string is enclosed in double quotation marks (" "). All Unicode characters can be placed within the quotation marks, except for characters that must be escaped (including quotation marks, backslashes, and control characters).

JSON text must be encoded in UTF-8, UTF-16, or UTF-32. The default encoding is UTF-8.

Here is an example:

{"Url":    "http://www.example.com/image/481989943"}

Create a JSON value

OceanBase Database supports the following DDL operations for the JSON data type:

• Create a table that contains a JSON column.

• Add or drop a JSON column.

• Create an index on a generated column that has the JSON data type.

• Enable semistructured encoding when you create a table in a MySQL tenant.

• Enable semistructured encoding for an existing table in a MySQL tenant.

Limitations

You can create multiple JSON columns in a table, subject to the following limitations:

• A JSON column cannot be a PRIMARY KEY, FOREIGN KEY, or UNIQUE KEY column. However, you can add a NOT NULL or CHECK constraint to a JSON column.

• A JSON column cannot have a default value.

• A JSON column cannot be a partitioning key.

• The length of a JSON value cannot exceed that of a LONGTEXT value. The maximum depth of a JSON object or array is 99.

Examples

Create or 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 column of 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 on a generated column that has the JSON data type

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 semistructured encoding

OceanBase Database allows you to enable semistructured encoding when you create a table in a MySQL tenant. The table-level parameter semistruct_encoding_type controls this feature. You must also set the row_format parameter to COMPRESSED. Otherwise, an error will be returned. If semistruct_encoding_type='encoding', the table is considered a semistructured table, and all JSON columns in the table will be encoded using semistructured encoding. If semistruct_encoding_type='', the table is considered a structured table.

1. Enable semistructured encoding

   Notice

   Before you enable semistructured encoding, make sure that the cluster parameter micro_block_merge_verify_level is set to the default value 2. Do not disable microblock merge verification.

   Example of enabling semistructured encoding when you create a table

   Example of enabling semistructured encoding for an existing table

   CREATE TABLE t1(
     j json
   ) row_format=COMPRESSED semistruct_encoding_type = 'encoding';
   

   For more information about the syntax, see CREATE TABLE.

   CREATE TABLE t1(j json);
   ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = 'encoding';
   

   For more information about the syntax, see ALTER TABLE.

2. Disable semistructured encoding

   Here is an example of disabling semistructured encoding:

   ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = '';
   

3. Query the semistructured encoding configuration

   You can execute the SHOW CREATE TABLE statement to query the semistructured encoding configuration. Here is an example:

   SHOW CREATE TABLE t1;
   

   The returned result is as follows:

   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                           |
   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t1    | CREATE TABLE `t1` (
     `j` json DEFAULT NULL
   ) 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_ENCODING_TYPE='ENCODING' |
   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   When semistruct_encoding_type=encoding', the query result displays the configuration item information, indicating that semistructured encoding is enabled.

Using semistructured encoding can improve the performance of JSON_VALUE() function queries. OceanBase Database optimizes the performance of JSON_VALUE expression queries based on the JSON semistructured encoding technology. Since the JSON data is split into subcolumns, the system can directly filter based on the encoded subcolumn data without needing to reconstruct the entire JSON structure. This significantly improves query efficiency.

Here is an example:

-- Query rows where the value of the name field is 'Devin'.
SELECT * FROM t WHERE JSON_VALUE(j_doc, '$.name' RETURNING CHAR) = 'Devin';

The following points apply to character sets:

• OceanBase Database uses the utf8_bin character set for JSON data.

• To ensure that the string white-box filtering feature works properly, set:

  SET @@collation_server = 'utf8mb4_bin';
  SET @@collation_connection='utf8mb4_bin';