How do I read and write JSON data - Lindorm - Alibaba Cloud Documentation Center

LindormTable allows you to use JSON data when you create tables, insert data, and update data. The JSON data type is widely used by various programming languages for data exchange. JSON data adopts a clear structure based on key-value pairs, which is easy to read and transmit.

Applicable engines

JSON data is applicable only to LindormTable

Prerequisites

The LindormTable version is 2.6.2 or later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Limits

The data type of the primary key columns in Lindorm wide tables cannot be set to JSON.

DDL

When you create a table or add columns to a table, you can set the data type of specific columns to JSON.

You can execute the following statement to create a table and set the data type of a column in the table to JSON. For more information about the syntax used to create a table, see CREATE TABLE.
```
CREATE TABLE tb (p1 INT, c1 VARCHAR, c2 JSON, PRIMARY KEY(p1));
```
You can execute the following statement to add the c3 column to a table and set the data type of the c3 column to JSON. For more information about the syntax used to add a column to a table, see ALTER TABLE.
```
ALTER TABLE tb ADD c3 JSON;
```
Note
When you add a column to a table, the table is not locked. You can still perform DML operations on the table.

In the preceding examples, the tb table contains a primary key column p1 whose data type is INT, a non-primary key column c1 whose data type is VARCHAR, a non-primary key column c2 whose data type is set to JSON when the table is created, and a non-primary key column c3 whose data type is set to JSON when the c3 column is added to the table. You can execute the following statement to query the schema of the tb table:

DESCRIBE tb;

Returned results:

+--------------+---------------------+------------------------+---------+----------------+------------+
| TABLE_SCHEMA |     TABLE_NAME      |      COLUMN_NAME       |  TYPE   | IS_PRIMARY_KEY | SORT_ORDER |
+--------------+---------------------+------------------------+---------+----------------+------------+
| default      | tb                  | p1                     | INT     | true           | ASC        |
| default      | tb                  | c1                     | VARCHAR | false          | none       |
| default      | tb                  | c2                     | JSON    | false          | none       |
| default      | tb                  | c3                     | JSON    | false          | none       |
+--------------+---------------------+------------------------+---------+----------------+------------+

DML

You can perform DML operations to write data to, read data from, or delete data from a column whose data type is JSON.

UPSERT

You can use one of the following three methods to write data to a JSON column. Only JSON objects or JSON strings can be written to a column whose data type is JSON. Otherwise, errors occur when you write data. LindormTable SQL provides the json_object and json_array functions to convert the data that is written to the table to JSON objects.

Write JSON strings to a JSON column. You can use the following methods to write a JSON string to a JSON column:

Use the Statement() method to execute SQL statements. This way, you can write a JSON string to a JSON column.

Connection conn = DriverManager.getConnection("Lindorm URL", properties);
Statement stmt = conn.createStatement();
String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}"; 
String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', '"+ jsonStr1 + "')"; 
// Returns the number of columns that is written to the table.
int ret = stmt.executeUpdate(upsertSQL);

Use the PrepareStatement() method to execute SQL statements. This way, the SQL statements are preprocessed and values are specified for the parameters in the SQL template.

String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
// Specify the SQL template used to write data.
String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', ?)";
PreparedStatement preStmt = conn.prepareStatement(upsertSQL);
// Write the JSON string to the JSON column,
preStmt.setString(1, jsonStr1);
int ret = stmt.executeUpdate();

Use the json_object function to convert the written data to a JSON object that consists of key-value pairs based on the writing order, and then write the JSON object to the JSON column.
```
String upsert = "UPSERT INTO tb(p1,c1,c2) VALUES(2,'2',json_object('k1', 2, 'k2', '2'))";
```
You can execute the following statement to write the data specified by the preceding statement by using LindormTable SQL:
```
UPSERT INTO tb(p1,c1,c2) VALUES(2,'2','{"k1":2,"k2":"2"}');
```
Use the json_array function to convert the written data to a JSON object in the array format based on the writing order, and then write the JSON object to the JSON column.
```
String  upsert = "UPSERT INTO " + tableName + "(p1,c1,c2) VALUES(3,'3', json_array(1, 2, json_object('k1', 3, 'k2', '3')))";
```
You can execute the following statement to write the data specified by the preceding statement by using LindormTable SQL:
```
UPSERT INTO tb(p1,c1,c2) VALUES(3,'3','[1,2,{"k1":3,"k2":"3"}]');
```

Verify the result

Execute the following statement to view the data written to the table:

SELECT * FROM tb;

SELECT

When you query the values in a JSON column, you must use the json_extract function to return the values or configure conditions to filter the values.

You can use the json_extract function in LindormTable SQL in a similar way as you use the function in MySQL. The json_extract function can be used in the SELECT or WHERE clause.

When the json_extract function is used in the SELECT clause, the function obtains the values in the JSON column and returns the values to the user. Examples:

Write the following data to the JSON column: "{\"k1\":1}".

String json = "{\"k1\":1}";
// Use the json_extract function in the SELECT clause to return the value of k1 in the c2 column and set the name of the column that stores the result to j.
String select = "select p1, c1, c2, json_extract(c2, '$.k1') j from tb where p1 = 1";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.
String resultC2k1 = resultSet.getString("j");
// resultC2k1 indicates the value of k1 in the c2 column, which is 1.

Write the following data to the JSON column: "{\"k1\":2,\"k2\":\"2\"}".

String json ="{\"k1\":2,\"k2\":\"2\"}";
// Use the json_extract function in the SELECT clause to return the value of k2 in the c2 column and set the name of the column that stores the result to j.
String select =  "select p1, c1, c2, json_extract(c2, '$.k2') j from tb where p1 = 2";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.
String resultC2k1 = resultSet.getString("j");
// resultC2k1 indicates the value of k2 in the c2 column, which is 2.

Write the following data to the JSON column: "[1,2,{\"k1\":3,\"k2\":\"3\"}]".

String json ="[1,2,{\"k1\":3,\"k2\":\"3\"}]";
// Use the json_extract function in the SELECT clause to return the value of k2 on the second index of the JSON array in the c2 column and set the name of the column that stores the result to j.
String "select json_extract(c2, '$[2].k2') j from  tb where p1 = 3";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.
String resultC2k1 = resultSet.getString("j");
// resultC2k1 indicates the value of k2 on the second index of the JSON array in the c2 column, which is 3.

When the json_extract function is used in the WHERE clause, the function obtains the values in the JSON column and filters out the values. If the json_extract function is used in the WHERE clause to compare and filter data, the method used to compare different types of data in LindormTable SQL is the same as that used in MySQL. For more information, see The JSON Data Type.

Write the following data to the JSON column: "{\"k1\":2,\"k2\":\"2\"}".

String json =  "{\"k1\":2,\"k2\":\"2\"}";
// Use the json_extract function in the WHERE clause to return the data rows in which the value of k2 in the c2 column is larger than 0.
String select = "select p1, c1, c2 from tb where where p1 >= 1 and p1 < 4 and json_extract(c2, '$.k2') > '0'";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.

Write the following data to the JSON column: "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}".

String json = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
// Use the json_extract function in the WHERE clause to return the data rows in which the value of the '$.k2.k3.k4' path of the c2 column is larger than 4.
String select = "select * from tb where p1 >= 4 and p1 < 6 and json_extract(c2, '$.k2.k3.k4') > 4";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.

Write the following data to the JSON column: "[1,2,{\"k1\":3,\"k2\":\"3\"}]".

String json = "[1,2,{\"k1\":3,\"k2\":\"3\"}]";
// Use the json_extract function in the WHERE clause to return the data rows in which the value of k2 on the second index in the JSON array in the c2 column is larger than 0. 
String select = "select * from  tb where p1 >= 1 and p1 < 4 and json_extract(c2, '$[2].k2') > '0'";
ResultSet resultSet = stmt.executeQuery(select);
resultSet.next();
String resultC2 = resultSet.getString(c2);
// resultC2 indicates the c2 column.

UPDATE

Important

The path used to execute the UPDATE statement must be in the map format. For example, if the value of the k1 path in the c2 column is 2, the value of the k1 path cannot be updated. If the value of the k1 path in the c2 column is {"k2":"value"}, the value of the k1 path can be updated. You can execute the UPSERT INTO statement to overwrite the original path value with a new value in the valid format before you execute the UPDATE statement.

To update data in the specified path of a JSON column, you must use the UPDATE syntax. The UPDATE syntax supports the following operations:

JSON_SET: updates the value in the specified path of a JSON column or adds a new path and the value in the path of a JSON column.
Example: If the k1.k2 path exists in the c2 column, the value in the k1.k2 path is updated to value. If the k1.k2 path does not exist in the c2 column, the k1.k2 path is added and the value in it is set to value. Before you add the k1.k2 path, make sure that the k1 path exists.
```
UPDATE tb SET c2 = JSON_SET(c2, '$.k1.k2', 'value') WHERE p1 = 2;
```
JSON_INERT: adds a value to a path that does not exist in a JSON column.
Example: If the k1.k2 path does not exist in the c2 column, the value of the k1.k2 path is set to nvalue. Before you add the value to the path, make sure that the k1 path exists.
```
UPDATE tb SET c2 = JSON_INSERT(c2 ,'$.k1.k2' ,'nvalue') WHERE p1 = 2;
```
JSON_REPLACE: updates the value of an existing path in a JSON column.
Example: If the k1 path exists in the c2 column, the value in the k1 path is updated to nvalue.
```
UPDATE tb SET c2 = JSON_REPLACE(c2 ,'$.k1' ,'nvalue') WHERE p1 = 2;
```
JSON_REMOVE: deletes an existing path in a JSON column and the value in the path.
Example: If the k1 path exists in the c2 column, the k1 path and the value in the path is deleted.
```
UPDATE tb SET c2 = JSON_REMOVE(c2 , '$.k1') WHERE p1 = 2;
```

Verify the result

Execute the following statement to view the updated data:

SELECT * FROM tb;

Create secondary indexes

LindormTable SQL allows you to create a secondary index for data in the specified path of a JSON column. You must specify the type of the json_extract function when you create the secondary index.

Syntax

create_index_statement ::=  CREATE INDEX [ index_name ]
                                ON table_name '(' index_identifier ')'
                              [INCLUDE include_identifier]
                              [ASYNC]
                                 [ index_options ]
index_identifier       ::=  '('json_extract_type(column, json_path)')'
include_identifier   ::= '('column_name1,...,column_namen ')'

Parameters

Parameter	Description
index_name	The name of the index table.
table_name	The name of the wide table.
json_extract_type	The type of the json_extract function that is used to extract fields of the specified data type from the JSON column to create the secondary index. If the type of data in the JSON column does not match the type of the json_extract function, the secondary index is not created. Valid values: json_extract_string json_extract_long
column	The name of the JSON column.
json_path	The path of the JSON column. The values used to create the index are extracted from the path.
ASYNC	Specifies that the search index is created asynchronously. If you do not specify this parameter, the search index is created synchronously.

Examples

Create a secondary index for the LONG data in the k1.k2 path of the c3 column and set the compression algorithm of the index table to ZSTD. If data in the k1.k2 path is not of the LONG type, the secondary index is not created. If data in the k1.k2 path is not of the LONG type, the secondary index is not created.

CREATE INDEX idx1 ON tb(json_extract_long(c2, '$.k1.k2')) INCLUDE(c1,c3) ASYNC 'COMPRESSION'='ZSTD';

Verify the result

Execute the following statement to view the index that is created:

SHOW INDEX FROM tb;