JSON array manipulation functions

JSON_ARRAY_INSERT

Inserts a value at a specified position in a JSON array. The path must point to an array element, such as $[0] or $.a[0].

Syntax:

JSON_ARRAY_INSERT(json_doc, path, value[, path2, value2] ...)

Parameters:

• json_doc: The JSON document to modify (as a string).

• path: The insertion position. Must point to an array element (such as $[0] or $.a[0]).

• value: The value to insert.

• You can specify multiple path-value pairs for a batch insert.

Return value: The modified JSON document as a string.

JSON_ARRAY_APPEND

Appends one or more values to the end of specified arrays within a JSON document.

Syntax:

JSON_ARRAY_APPEND(json_doc, path, value[, path2, value2] ...)

Parameters:

• json_doc: The JSON document to modify (as a string).

• path: The target array path, such as $, $[1], or $.hobby.

• value: The value to append.

• You can specify multiple path-value pairs for a batch append.

Return value: The modified JSON document as a string.

Basic operations

-- Create a table
CREATE TABLE t (id INT, c2 JSON, tags JSON, PRIMARY KEY(id)) 'CONSISTENCY' = 'strong';

-- Initial data
INSERT INTO t(id, tags, c2) VALUES
  (1, '["a","b","c"]',               '[1,[2,3]]'),
  (2, '[1,2,3]',                      '[1,2,3]'),
  (3, NULL,                           '"hello"'),
  (4, NULL,                           '{"key":"value"}'),
  (5, NULL,                           '{"name":"Tim","hobby":["car"]}'),
  (6, NULL,                           '{"field":"value"}'),
  (7, NULL,                           '{"tags":["java"],"categories":["tech"]}');

-- Insert at a specified position, shifting subsequent elements to the right
UPDATE t SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'first') WHERE id = 1;
-- Original: ["a","b","c"], Result: ["first","a","b","c"]

-- Append to the end of the array
UPDATE t SET tags = JSON_ARRAY_APPEND(tags, '$', 'last') WHERE id = 2;
-- Original: [1,2,3], Result: [1,2,3,"last"]

-- Operate on a nested array (paths support arbitrary depth)
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[1][0]', 'x') WHERE id = 1;
-- Original: [1,[2,3]], Result: [1,["x",2,3]]

-- Append to an array within an object
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.hobby', 'food') WHERE id = 5;
-- Original: {"name":"Tim","hobby":["car"]}, Result: {"name":"Tim","hobby":["car","food"]}

-- INSERT: Fails if the path does not point to an array.
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'x') WHERE id = 3;
-- Original: "hello" (scalar), Error: Path does not point to array

UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'x') WHERE id = 4;
-- Original: {"key":"value"} (object), Error: Path does not point to array

-- APPEND: Wraps a non-array node in an array and then appends the new value.
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$', 'world') WHERE id = 3;
-- Original: "hello", Result: ["hello","world"]

UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$', 'item') WHERE id = 4;
-- Original: {"key":"value"}, Result: [{"key":"value"},"item"]

-- Convert a scalar field within an object to an array in place
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.field', 'new') WHERE id = 6;
-- Original: {"field":"value"}, Result: {"field":["value","new"]}

Batch operations

Each path-value pair executes sequentially — the result of each step becomes the input for the next, and indices reflect the current array state:

-- Batch insert: Indices are re-evaluated after each insertion.
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'first', '$[3]', 'middle', '$[6]', 'last') WHERE id = 2;
-- Original: [1,2,3], Result: ["first",1,2,"middle",3,"last"]

-- Multiple insertions at the same position: Later insertions appear first.
UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[0]', 'a', '$[0]', 'b') WHERE id = 2;
-- Original: [1,2,3], Result: ["b","a",1,2,3]

-- Batch append to multiple sub-arrays
UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, '$.tags', 'sql', '$.tags', 'python', '$.categories', 'db') WHERE id = 7;
-- Original: {"tags":["java"],"categories":["tech"]}
-- Result: {"tags":["java","sql","python"],"categories":["tech","db"]}

Index out of bounds

If the index exceeds the array length, the value is appended to the end. This matches MySQL behavior.

UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, '$[100]', 'x') WHERE id = 2;
-- Original: [1,2,3], Result: [1,2,3,"x"]

Parameterization with PreparedStatement

You can use the ? placeholder for both the path and value parameters.

// Initial data for id=1: c2 = ["a","inserted","b","c","appended"]

// JSON_ARRAY_INSERT: Insert at position $[2]
PreparedStatement pstmt = conn.prepareStatement(
    "UPDATE t SET c2 = JSON_ARRAY_INSERT(c2, ?, ?) WHERE id = ?");
pstmt.setString(1, "$[2]");
pstmt.setString(2, "prep_insert");
pstmt.setInt(3, 1);
pstmt.executeUpdate();
// Result: ["a","inserted","prep_insert","b","c","appended"]

// JSON_ARRAY_APPEND: Append to the end of the array
pstmt = conn.prepareStatement(
    "UPDATE t SET c2 = JSON_ARRAY_APPEND(c2, ?, ?) WHERE id = ?");
pstmt.setString(1, "$");
pstmt.setString(2, "prep_append");
pstmt.setInt(3, 1);
pstmt.executeUpdate();
// Result: ["a","inserted","prep_insert","b","c","appended","prep_append"]

Node type and NULL handling