Lindorm:JSON functions

json_object

Creates a JSON object from key-value pairs.

Syntax

json_object(key1, value1, key2, value2, ...)

Parameters

Examples

Create a simple object:

SELECT json_object('name', 'Alice', 'age', 25);
-- Result: {"name": "Alice", "age": 25}

Use in an UPSERT statement:

UPSERT INTO users (id, data) VALUES (1, json_object('name', 'Charlie', 'city', 'Beijing'));

json_array

Creates a JSON array from a list of values.

Syntax

json_array(value1, value2, value3, ...)

Parameters

Examples

Create a simple array:

SELECT json_array('apple', 'banana', 'orange');
-- Result: ["apple", "banana", "orange"]

Use in an UPSERT statement:

UPSERT INTO products (id, tags) VALUES (1, json_array('electronics', 'mobile', 'smartphone'));

json_extract

Extracts a value from a JSON document at a specified path and returns it in its native JSON type.

Syntax

json_extract(json_column, 'path')

Parameters

Examples

Extract a top-level field:

SELECT json_extract('{"name": "Alice", "age": 25}', '$.name');
-- Result: "Alice"

Extract a nested field:

SELECT json_extract('{"user": {"name": "Bob", "age": 30}}', '$.user.name');
-- Result: "Bob"

Extract an array element:

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills[0]');
-- Result: "Java"

Extract an entire array:

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills');
-- Result: ["Java", "Python"]

Use in a WHERE clause with a numeric comparison:

SELECT * FROM tb WHERE json_extract(c2, '$.k3.k4') > 5;

Use in a WHERE clause with a string comparison (use json_extract_string for type safety):

SELECT * FROM tb WHERE json_extract_string(c2, '$.k2') = '1';

json_extract_string

Extracts a value at a specified path and returns it as VARCHAR. If the value at the path is not a string, the function returns NULL.

Examples

The value at $.name is a string, so it returns a VARCHAR:

SELECT json_extract_string('{"name": "Alice"}', '$.name');
-- Result: Alice (VARCHAR)

The value at $.number is a number, not a string, so the function returns NULL:

SELECT json_extract_string('{"number": 30}', '$.number');
-- Result: NULL

json_extract_long

Extracts a value at a specified path and returns it as LONG. If the value at the path is not a number, Java Database Connectivity (JDBC) throws an error.

Examples

The value is an integer, so it returns a LONG:

SELECT json_extract_long('{"id": 123456789}', '$.id');
-- Result: 123456789 (LONG)

The value is a floating-point number — JDBC throws an error:

SELECT json_extract_long('{"id": 123456.789}', '$.id');
-- Throws an error

The value is a numeric string — JDBC throws an error:

SELECT json_extract_long('{"id": "123456.789"}', '$.id');
-- Throws an error

json_extract_double

Extracts a value at a specified path and returns it as Double. If the value at the path is not a floating-point number, JDBC throws an error.

Examples

The value is a floating-point number, so it returns a Double:

SELECT json_extract_double('{"id": 12345.56}', '$.id');
-- Result: 12345.56 (Double)

The value is an integer — the function returns it as a Double:

SELECT json_extract_double('{"id": 12345}', '$.id');
-- Result: 12345.0 (Double)

The value is a numeric string — JDBC throws an error:

SELECT json_extract_double('{"id": "123456.789"}', '$.id');
-- Throws an error

json_contains

Returns 1 if target_json contains all elements in candidate_json, and 0 otherwise. Optionally, specify a path to check containment at a specific location within the document.

Returns NULL if the specified path does not exist or if any parameter is NULL.

Syntax

json_contains(target_json, candidate_json[, path])

Parameters

How to format `candidate_json`

candidate_json must be a valid JSON string representing the element to check for:

JSON type classifications

• Scalar: String, Number (Integer/Double), Boolean, Null

• Complex type: Array, Object

Containment rules

• A scalar can only contain another scalar, not an array.

• An array can contain an element (scalar) or a subset array.

Examples

Set up the example table used in the following queries:

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, data) VALUES (1, NULL);
UPSERT INTO test_table (id, data) VALUES (2, '{"skills": ["Java", "Python"]}');
UPSERT INTO test_table (id, data) VALUES (3, '{"skills": ["Go", "C"]}');
UPSERT INTO test_table (id, data) VALUES (4, '{"technical_skills": ["Java", "Go", "Rust"]}');
UPSERT INTO test_table (id, data) VALUES (5, '["Java","C++", "JavaScript"]');
UPSERT INTO test_table (id, data) VALUES (6, '{"skills": "Java"}');
UPSERT INTO test_table (id, data) VALUES (7, '["Java", "C#"]');
UPSERT INTO test_table (id, data) VALUES (8, '{"skills": ["Go", "Rust"]}');

The two-parameter form checks the entire document (root path $). This finds rows where the top-level document is an array containing "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]') AND id > 0 AND id < 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

The three-parameter form checks a specific path. This finds rows where $.skills is an array containing "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+

json_contains requires all elements of candidate_json to be present. This query finds no rows because no $.skills array contains both "Java" and "Go":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java","Go"]', '$.skills') AND id > 0 AND id < 10;
-- Empty set

Using a scalar "Java" as the candidate matches both an array containing "Java" and a scalar equal to "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '"Java"', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    6 | {"skills": "Java"}             |
+------+--------------------------------+

Check if an object contains a key-value pair:

SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');
-- Result: 1

Check if a specific path contains a given value:

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.a');
-- Result: 1

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.b');
-- Result: 0

json_contains_any

Returns 1 if target_json contains at least one element from candidate_json. Uses the same syntax and parameters as json_contains.

Examples

Without a path, checks the root document. The top-level document is an object, not an array containing "Java" or "Go" directly:

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
-- Result: 0

With a path, checks $.skills. The array at $.skills contains "Java", which matches one element of the candidate:

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]', '$.skills');
-- Result: 1

The two-parameter form finds rows where the top-level document is an array with any element from ["Java", "Go"]:

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go"]') LIMIT 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

The three-parameter form finds rows where $.skills contains any element from the candidate list:

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go", "Rust"]', '$.skills') LIMIT 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    3 | {"skills": ["Go", "C"]}        |
|    6 | {"skills": "Java"}             |
|    8 | {"skills": ["Go", "Rust"]}     |
+------+--------------------------------+

Check if a specific path contains any value from the candidate array:

SELECT json_contains_any('{"departments": ["Engineering", "Sales"]}', '["Marketing", "Engineering"]', '$.departments');
-- Result: 1

Understanding scalar vs. array containment at a path

Consider json_contains_any(data, '["Java"]', '$.technical_skills'). The behavior depends on the JSON type at $.technical_skills:

• If the value is {"technical_skills": "Java"} (a scalar string), the function returns false. A subset of a scalar cannot be a list.

• If the value is {"technical_skills": ["Java"]} or {"technical_skills": ["Java", "Go"]} (an array), the function returns true.

Consider json_contains_any(data, '"Java"', '$.technical_skills'). Using a scalar candidate:

• If the value is {"technical_skills": "Java"} (a scalar string equal to "Java"), the function returns true.

• If the value is {"technical_skills": ["Java"]} or {"technical_skills": ["Java", "Go"]} (an array containing "Java"), the function returns true.

json_set

Inserts or updates a value at a specified path. If the path exists, updates the value. If the path does not exist, inserts the value. If the JSON column is NULL, the result is NULL — no new object is created.

Equivalent to json_insert + json_replace.

Syntax

json_set(json_column, 'path', new_value)

Examples

Update an existing field:

UPSERT INTO test_table (id, data) VALUES (3, '{"name": "Charlie", "age": 30}');
UPDATE test_table SET data = json_set(data, '$.age', 31) WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31}

Insert a new field:

UPDATE test_table SET data = json_set(data, '$.department', 'Engineering') WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31, "department": "Engineering"}

If the JSON column is NULL, the result remains NULL:

UPSERT INTO test_table (id, c1) VALUES (4, 'test');
UPDATE test_table SET data = json_set(data, '$.name', 'David') WHERE id = 4;
-- Result: NULL

json_insert

Inserts a value at a specified path only if the path does not already exist. If the path already exists, the function takes no action.

json_insert does not overwrite existing values. An attempt to insert into an existing field silently does nothing — the original value is preserved.

Syntax

json_insert(json_column, 'path', new_value)

Examples

Insert a new field:

UPSERT INTO test_table (id, data) VALUES (5, '{"name": "Eve"}');
UPDATE test_table SET data = json_insert(data, '$.age', 28) WHERE id = 5;
-- Result: {"name": "Eve", "age": 28}

Attempting to insert into an existing field has no effect — the name field remains unchanged:

UPDATE test_table SET data = json_insert(data, '$.name', 'New Name') WHERE id = 5;
-- Result: {"name": "Eve", "age": 28}

json_replace

Updates a value at a specified path only if the path already exists. If the path does not exist, the function takes no action.

Syntax

json_replace(json_column, 'path', new_value)

Examples

Update an existing field:

UPSERT INTO test_table (id, data) VALUES (6, '{"name": "Frank", "age": 35}');
UPDATE test_table SET data = json_replace(data, '$.age', 36) WHERE id = 6;
-- Result: {"name": "Frank", "age": 36}

Attempting to update a non-existent field has no effect — no city field is added:

UPDATE test_table SET data = json_replace(data, '$.city', 'Shanghai') WHERE id = 6;
-- Result: {"name": "Frank", "age": 36}

json_remove

Deletes the value at a specified path and returns the modified document. If the path does not exist, the operation succeeds without error.

Syntax

json_remove(json_column, 'path')

Examples

Delete a field:

UPSERT INTO test_table (id, data) VALUES (7, '{"name": "Grace", "temp_field": "to_remove"}');
UPDATE test_table SET data = json_remove(data, '$.temp_field') WHERE id = 7;
-- Result: {"name": "Grace"}

Deleting a non-existent field does nothing and returns the document unchanged:

UPDATE test_table SET data = json_remove(data, '$.nonexistent') WHERE id = 7;
-- Result: {"name": "Grace"}

json_upsert

Inserts or updates a value at a specified path. Behaves like json_set, with one key difference: if the JSON column is NULL, json_upsert creates a new JSON object instead of returning NULL.

Syntax

json_upsert(json_column, 'path', new_value)

Examples

When the JSON column is NULL, json_upsert creates a new object:

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, c1) VALUES (1, 'test');
UPDATE test_table SET data = json_upsert(data, '$.name', 'Alice') WHERE id = 1;
-- Result: {"name": "Alice"}

Update an existing field:

UPSERT INTO test_table (id, data) VALUES (2, '{"name": "Bob", "age": 25}');
UPDATE test_table SET data = json_upsert(data, '$.age', 26) WHERE id = 2;
-- Result: {"name": "Bob", "age": 26}

Insert a new field:

UPDATE test_table SET data = json_upsert(data, '$.city', 'Beijing') WHERE id = 2;
-- Result: {"name": "Bob", "age": 26, "city": "Beijing"}

Containment check functions

Update functions

Syntax

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

Parameters

Supported functions

The following functions support function-based index creation:

• json_extract_string(json_column, 'path') — indexes string values

• json_extract_long(json_column, 'path') — indexes long integer values

• json_extract_double(json_column, 'path') — indexes floating-point values

• json_contains(json_column, 'value', 'path') — indexes containment checks; the value and path parameters must be fixed (static) values

Examples

Create an index to accelerate json_contains queries on $.roles:

CREATE INDEX idx_user_role ON test_table (json_contains(data, '"admin"', '$.roles')) INCLUDE(data) SYNC;

Create an index on a string field at $.address.city:

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city')) INCLUDE(data) SYNC;

Create an index on a string field without including additional columns:

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city'));

Create an index on a numeric field at $.age:

CREATE INDEX idx_user_age ON test_table (json_extract_long(data, '$.age')) INCLUDE(data) SYNC;

Verify the index was created:

SHOW INDEX FROM test_table;

For JSON search index support, see Use search indexes for the Lindorm JSON type.