Create a JSON value

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

JSON text structure

The JSON text structure consists of characters, strings, numbers, and three literal names. Any number of separators, including spaces, horizontal tabs, line breaks, and carriage returns, can appear before or after any structure character.

       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 (or members) enclosed in braces. The names in an object must be unique. A name is a string, 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 brackets. Array elements are separated by commas. 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 can have an integer component, an optional minus sign (-) prefix, and a fractional part and/or an exponent part. Leading zeros are not allowed. The fractional part is a decimal point followed by one or more digits. The exponent part starts with an uppercase or lowercase E, followed by an optional plus sign (+) or minus sign (-). After 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 inside the quotation marks, except for characters that must be escaped (such as 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.

Here is an example:

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

Create a JSON value

seekdb supports the following DDL operations on JSON data:

• Create a table with a JSON column.

• Add or drop a JSON column.

• Create an index on a generated column of the JSON type.

• Enable semi-structured encoding when you create a table.

• Enable semi-structured encoding for an existing table.

Limitations

You can create multiple JSON columns in a table. However, the following limitations apply:

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

• A JSON column cannot have a default value.

• A JSON column cannot be a partitioning key.

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

Examples

Create or modify a JSON column

CREATE TABLE tbl1 (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected

ALTER TABLE tbl1 MODIFY docs JSON CHECK(docs <'{"a" : 100}');
Query OK, 0 rows affected

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 for JSON data',
      json_name  VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
      index      json_info_id_idx (json_id)
    )COMMENT 'Example table for JSON';
Query OK, 0 rows affected

ALTER TABLE json_tab ADD COLUMN json_info1 JSON;
Query OK, 0 rows affected

ALTER TABLE json_tab ADD INDEX (json_name);
Query OK, 0 rows affected

ALTER TABLE json_tab drop COLUMN json_info1;
Query OK, 0 rows affected

Create an index on a generated column

CREATE TABLE jn ( c JSON, g INT GENERATED ALWAYS AS (c->"$.id"));
Query OK, 0 rows affected

CREATE INDEX idx1 ON jn(g);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

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

SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;

+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G

*************************** 1. row ***************************
Query Plan: ====================================================
*************************** 2. row ***************************
Query Plan: |ID|OPERATOR        |NAME    |EST.ROWS|EST.TIME(us)|
*************************** 3. row ***************************
Query Plan: ----------------------------------------------------
*************************** 4. row ***************************
Query Plan: |0 |TABLE RANGE SCAN|jn(idx1)|2       |83          |
*************************** 5. row ***************************
Query Plan: ====================================================
*************************** 6. row ***************************
Query Plan: Outputs & filters:
*************************** 7. row ***************************
Query Plan: -------------------------------------
*************************** 8. row ***************************
Query Plan:   0 - output([JSON_UNQUOTE(JSON_EXTRACT(jn.c, '$.name'))]), filter(nil), rowset=16
*************************** 9. row ***************************
Query Plan:       access([jn.__pk_increment], [jn.c]), partitions(p0)
*************************** 10. row ***************************
Query Plan:       is_index_back=true, is_global_index=false,
*************************** 11. row ***************************
Query Plan:       range_key([jn.g], [jn.__pk_increment]), range(NULL,MAX ; 2,MAX),
*************************** 12. row ***************************
Query Plan:       range_cond([jn.g <= 2])
12 rows in set (0.002 sec)

Use semi-structured encoding

To enable semi-structured encoding when you create a table, set the table-level parameter semistruct_encoding_type to encoding and set the row_format parameter to COMPRESSED. Otherwise, an error will be returned. When semistruct_encoding_type='encoding', the table is considered a semi-structured table, which means that all JSON columns in the table will be enabled for semi-structured encoding. When semistruct_encoding_type='', the table is considered a structured table.

1. Enable semi-structured encoding

   tip

   If you enable semi-structured encoding, make sure that the configuration item micro_block_merge_verify_level is set to the default value 2. Do not disable microblock merge verification.

   tab Example of enabling semi-structured encoding when you create a table

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

   For more information about the syntax, see CREATE TABLE.

   tab Example of enabling semi-structured encoding for an existing 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 semi-structured encoding

   An example of disabling semi-structured encoding is as follows:

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

3. Query the semi-structured encoding configuration

   You can execute the SHOW CREATE TABLE statement to query the semi-structured encoding configuration. Example statement:

   SHOW CREATE TABLE t1;

   The 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 |
   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.003 sec)

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

Using semi-structured encoding improves the performance of JSON_VALUE() queries. Based on the JSON semi-structured encoding technology, seekdb optimizes the performance of JSON_VALUE expression-based filtering queries. Since JSON data is split into subcolumns, the system can directly filter based on the encoded subcolumn data without reconstructing the entire JSON structure, significantly improving query efficiency.

Example query:

-- 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 character set considerations apply:

• seekdb uses utf8_bin to encode JSON data.

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

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