JSON data type

UPSERT

Only JSON objects and JSON arrays can be written to a JSON column. Writing any other value returns an error.

Method 1: Raw JSON string via Statement

Use Statement.executeUpdate() with the JSON string embedded directly in the SQL:

Connection conn = DriverManager.getConnection("Lindorm URL", properties);
Statement stmt = conn.createStatement();

String jsonStr = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
String sql = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', '" + jsonStr + "')";

// Returns the number of rows written.
int ret = stmt.executeUpdate(sql);

Method 2: Parameterized query via PreparedStatement

Use PreparedStatement to bind the JSON string as a parameter, which avoids string concatenation and SQL injection risks:

String jsonStr = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";

// Prepare the statement template.
String sql = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', ?)";
PreparedStatement preStmt = conn.prepareStatement(sql);

// Bind the JSON string to the placeholder.
preStmt.setString(1, jsonStr);
int ret = preStmt.executeUpdate();

Method 3: json_object and json_array functions

Use json_object to build a JSON object from key-value pairs, or json_array to build an array. Both functions can be composed.

-- json_object: alternating keys and values become a JSON object.
UPSERT INTO tb(p1,c1,c2) VALUES(2,'2', json_object('k1', 2, 'k2', '2'));
-- Equivalent stored value: {"k1":2,"k2":"2"}

-- json_array: positional values become a JSON array; nested objects are supported.
UPSERT INTO tb(p1,c1,c2) VALUES(3,'3', json_array(1, 2, json_object('k1', 3, 'k2', '3')));
-- Equivalent stored value: [1,2,{"k1":3,"k2":"3"}]

Verify the result

SELECT * FROM tb;

SELECT

Use json_extract(column, path) to read values from a JSON column. The function follows the same path syntax as MySQL. For path syntax details, see The JSON Data Type.

Extract a value in the SELECT clause

// Extract the value of k1 from the c2 column. The result column is aliased to j.
String select = "SELECT p1, c1, c2, json_extract(c2, '$.k1') j FROM tb WHERE p1 = 1";
ResultSet rs = stmt.executeQuery(select);
rs.next();

String c2Value = rs.getString("c2"); // Full JSON column value
String k1Value = rs.getString("j");  // Value of k1, returned as a string

Use array-index syntax to extract from JSON arrays:

// Data in c2: [1,2,{"k1":3,"k2":"3"}]
// Extract k2 from the object at index 2 of the array.
String select = "SELECT json_extract(c2, '$[2].k2') j FROM tb WHERE p1 = 3";
ResultSet rs = stmt.executeQuery(select);
rs.next();
String k2Value = rs.getString("j"); // Returns "3"

Filter rows with json_extract in the WHERE clause

Comparison behavior in the WHERE clause follows MySQL rules.

// Return rows where k2 in c2 is greater than 0, across a range of primary keys.
String select = "SELECT p1, c1, c2 FROM tb WHERE p1 >= 1 AND p1 < 4 AND json_extract(c2, '$.k2') > '0'";

// Return rows where the nested path k2.k3.k4 is greater than 4.
String select = "SELECT * FROM tb WHERE p1 >= 4 AND p1 < 6 AND json_extract(c2, '$.k2.k3.k4') > 4";

// Return rows where k2 at array index 2 is greater than 0.
String select = "SELECT * FROM tb WHERE p1 >= 1 AND p1 < 4 AND json_extract(c2, '$[2].k2') > '0'";

UPDATE

Use the following functions to update specific paths in a JSON column:

JSON_SET — update an existing path or add a new one

If the path exists, its value is updated. If it does not exist, the path and value are added. The parent path must already exist.

-- Sets $.k1.k2 to 'value'. Requires that $.k1 already exists.
UPDATE tb SET c2 = JSON_SET(c2, '$.k1.k2', 'value') WHERE p1 = 2;

JSON_INSERT — add a value to a path that does not exist

Has no effect if the path already exists.

-- Adds $.k1.k2 with value 'nvalue' only if $.k1.k2 does not exist.
-- Requires that $.k1 already exists.
UPDATE tb SET c2 = JSON_INSERT(c2, '$.k1.k2', 'nvalue') WHERE p1 = 2;

JSON_REPLACE — update a value at an existing path

Has no effect if the path does not exist.

-- Updates $.k1 to 'nvalue' only if $.k1 exists.
UPDATE tb SET c2 = JSON_REPLACE(c2, '$.k1', 'nvalue') WHERE p1 = 2;

JSON_REMOVE — delete a path and its value

Has no effect if the path does not exist.

-- Deletes $.k1 and its value if $.k1 exists.
UPDATE tb SET c2 = JSON_REMOVE(c2, '$.k1') WHERE p1 = 2;

Verify the result

SELECT * FROM tb;

Syntax

CREATE INDEX [index_name]
    ON table_name (json_extract_type(column, json_path))
  [INCLUDE (column_name1, ..., column_nameN)]
  [ASYNC]
  [index_options]

Parameters

Example

Create a secondary index on the LONG value at path $.k1.k2 in column c2, including columns c1 and c3 in the index, with ZSTD compression:

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

Verify the result

SHOW INDEX FROM tb;