The JavaScript Object Notation (JSON) data type is a special type used in databases to store and process JSON data. A JSON value must consist of objects (JSON objects), arrays, strings, numbers, boolean values (false/true), or null. The values false, true, and null are only allowed in lowercase.
JSON text structure
The JSON text structure consists of characters, strings, numbers, and three literal names. Various whitespace characters, such as spaces, horizontal tabs, line breaks, and carriage returns, can be used before or after any structural character.
Begin array = [ Left bracket
Begin object = { Left brace
End array = ] Right bracket
End object = } Right brace
Name separator = : Colon
Value separator = , Comma
Objects
The structure of an object is represented by a pair of braces containing zero or more name/value pairs (or members). The names within an object must be unique. A name is a string, and each name is followed by a colon to separate it from its value. A single comma is used to separate multiple name/value pairs.
Here is an example:
{ "NAME": "SAM", "Height": 175, "Weight": 100, "Registered" : false}
Arrays
The structure of an array is represented by a pair of brackets containing zero or more values (also known as elements). 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
Numbers are represented in decimal format and can include an integer component, an optional minus sign (-), a fractional part, and/or an exponent part. Leading zeros are not allowed. The fractional part consists of a decimal point followed by one or more digits. The exponent part starts with an uppercase or lowercase letter E, which can be 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]
Strings
A string starts and ends with quotation marks ("). All Unicode characters can be placed within quotation marks, except for characters that must be escaped (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.
Here is an example:
{"Url": "http://www.example.com/image/481989943"}
Create JSON values
OceanBase Database supports the following DDL operations for the JSON data type:
Creating a table with a JSON column.
Adding or dropping a JSON column.
Creating an index on a generated column of the JSON data type.
Limitations
You can create multiple JSON columns in a table, but the following limitations apply:
JSON columns cannot be used as
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEYconstraints. However, you can addNOT NULLorCHECKconstraints to JSON columns.JSON columns cannot have default values.
JSON columns 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.
Type conversion
You can convert JSON data between the JSON data type and the TEXT, MEDIUMTEXT, and LONGTEXT data types.
Examples
Example 1: Basic DDL operations
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
Example 2: Create an index on a generated column that specifies a 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