Create a JSON value

A JSON value must be an object (JSON object), array, string, number, Boolean (false/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 number of separators, including spaces, tabs, line breaks, and carriage returns, can precede or follow any of the structure characters.

       Start array =  [ Left bracket

       Start object =  { Left brace

       End array =  ] Right bracket

       End object =  } Right brace

       Name separator = : Colon

       Value separator = , Comma

Object

The structure of an object is represented as a pair of braces that contain 0 or more name/value pairs (or members). 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}

Array

The structure of an array is represented as a pair of brackets that contain 0 or more values (also called elements). The elements in an array are separated by commas. The values in an array do not have to be the same.

Here is an example:

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

Number

A number is represented in decimal format and consists of an integer component, which may be prefixed with an optional minus sign (-), followed by 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 (-). The E and optional sign are followed by one or more digits.

Here is an example:

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

String

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

The 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 with a JSON column.

• Add or drop a JSON column.

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

Limitations

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

• JSON columns cannot be used as PRIMARY KEY, FOREIGN KEY, or UNIQUE KEY columns. However, you can add NOT NULL or CHECK constraints to JSON columns.

• JSON columns cannot have default values.

• JSON columns cannot be used as partitioning keys.

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

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

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