How to replace child members of a JSON (JavaScript Object Notation) value using the JSON_REPLACE() function?

JSON_REPLACE(json, path1, val1, path2, val2, ...) is a MySQL built-in function that replaces child members of a JSON value at given locations. Each path-value argument pair specifies existing member locations and replace-by values. Non-existing member locations are ignored. The path argument uses the "$", "$.key" or "$[i]" format. The value can be a JSON scalar (String, Number, Boolean, or Null), a JSON array, or a JSON object.

JSON scalars can be specified as literals of MySQL equivalent data types. For example:

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_REPLACE(@j, '$[0]', 10, '$[2]', '[true, false]');
  -- +-------------------------------------------------------+
  -- | JSON_REPLACE(@j, '$[0]', 10, '$[2]', '[true, false]') |
  -- +-------------------------------------------------------+
  -- | [10, {"b": [1, 2]}, "[true, false]"]                  |
  -- +-------------------------------------------------------+

SELECT JSON_REPLACE(@j, '$[1].b', '[true, false]', '$[9]', 'End');
  -- +------------------------------------------------------------+
  -- | JSON_REPLACE(@j, '$[1].b', '[true, false]', '$[9]', 'End') |
  -- +------------------------------------------------------------+
  -- | ["a", {"b": "[true, false]"}, [3, 4]]                      |
  -- +------------------------------------------------------------+

SELECT JSON_REPLACE(@j, '$', '[true, false]');
  -- +----------------------------------------+
  -- | JSON_REPLACE(@j, '$', '[true, false]') |
  -- +----------------------------------------+
  -- | "[true, false]"                        |
  -- +----------------------------------------+

However, JSON arrays and JSON objects need to be constructed with CAST(), JSON_ARRAY(), or JSON_OBJECT() functions. For example:

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_REPLACE(@j, '$[2]', CAST('[true, false]' AS JSON));
  -- +---------------------------------------------------------+
  -- | JSON_REPLACE(@j, '$[2]', CAST('[true, false]' AS JSON)) |
  -- +---------------------------------------------------------+
  -- | ["a", {"b": [1, 2]}, [true, false]]                     |
  -- +---------------------------------------------------------+

SELECT JSON_REPLACE(@j, '$[2]', JSON_ARRAY(true, false));
  -- +---------------------------------------------------+
  -- | JSON_REPLACE(@j, '$[2]', JSON_ARRAY(true, false)) |
  -- +---------------------------------------------------+
  -- | ["a", {"b": [1, 2]}, [true, false]]               |
  -- +---------------------------------------------------+

SELECT JSON_REPLACE(@j, '$[2]', JSON_OBJECT('PI',3.14));
  -- +--------------------------------------------------+
  -- | JSON_REPLACE(@j, '$[2]', JSON_OBJECT('PI',3.14)) |
  -- +--------------------------------------------------+
  -- | ["a", {"b": [1, 2]}, {"PI": 3.14}]               |
  -- +--------------------------------------------------+

Reference information of the JSON_REPLACE() function:

JSON_REPLACE(json, path1, val1, path2, val2, ...): modjson
  Replaces child members of a JSON value at given locations, which
  are specified by path arguments in the "$", "$.key" or "$[i]" format.

Arguments, return value and availability:
  json: Required. The JSON value to be updated.
  path1, val1, path2, val2, ...: One or more JSON path-value pairs
    to be processed.
  modjson: Return value. The updated JSON value.
  Available since MySQL 5.7.

Related MySQL functions:

• JSON_INSERT() - Inserting Members to JSON Value
• JSON_REMOVE() - Removing JSON Child Members
• JSON_REPLACE() - Replacing JSON Child Members
• JSON_SET() - Inserting/updating JSON Child Members

⇒ JSON_SCHEMA_VALID() - JSON Schema Validation

⇐ JSON_REMOVE() - Removing JSON Child Members

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References