A JSON value must consist of JSON objects, arrays, strings, numbers, Boolean values (true or false), or nulls. The false, true, and null values must be in lowercase.
JSON text
A JSON text consists of structural characters, strings, numbers, and three literal names. Various delimiters, including spaces, tabs, line breaks, and carriage returns, are allowed 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 as a pair of braces that enclose zero or more name/value pairs or members. The names within an object must be unique. A name is a string. Each name is followed by a colon to separate it from its value. A single comma separates a value from a name that follows. A single comma is added to separate two name/value pairs. Here is an example:
{ "NAME": "SAM", "Height": 175, "Weight": 100, "Registered" : false}
Arrays
The structure of an array is represented as a pair of brackets that enclose zero or more values. A value is also known as an element. Elements in an array are separated by commas (,), and values in the array can be different.
Here is an example:
["abc", 10, null, true, false]
Numbers
Decimal numbers are used. A number contains an integer component that is prefixed with an optional minus sign (-) and may be followed by a fractional part and/or an exponent part. Leading zeros are not allowed. The fractional part consists of a decimal point and one or more following digits. The exponent part starts with an uppercase or lowercase E, which is followed by an optional plus sign (+) or a minus sign (-). The letter E and the optional sign can be followed by one or more digits.
Here is an example:
[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]
Strings
A string starts and ends with a double quotation mark ("). All Unicode characters can be enclosed in double quotation marks, except for the following characters that must be escaped: quotation marks, backslashes, and control characters.
The JSON text must be encoded in UTF-8, UTF-16, or UTF-32. By default, the JSON text is encoded in UTF-8.
Here is an example:
{"Url": "http://www.example.com/image/481989943"}
Create a JSON value
OceanBase Database supports the following DDL operations on the JSON data type:
Create a table with JSON columns.
Add or remove JSON columns.
Create an index on a generated column for indexing the JSON column.
Limitations
You can create multiple JSON columns in each table, but the following limitations apply:
A JSON column cannot be used as the
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEY, but you can add theNOT NULLorCHECKconstraint.A JSON column cannot contain default values.
JSON columns cannot be used as partitioning keys.
The length of the JSON data cannot exceed the value of
LONGTEXT. The maximum depth of each JSON object or array is 99.
Examples
Example 1: Perform 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 JSON data field',
json_name VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
index json_info_id_idx (json_id)
)COMMENT 'json sample 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 for the specified key by using a generated column
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