SQLite json_patch() Function

The SQLite json_patch() function merges and patchs the second JSON object to the original JSON object, and returns the patched original JSON object. If both arguments are not JSON objects, the second argument is returned.

The SQLite json_patch() function cans be used to modify JSON objects, such as: adding new members, modifying members, and deleting members.

Syntax

Here is the syntax of the SQLite json_patch() function:

json_patch(original_json, patch_json)

Parameters

original_json

Required. The original JSON document to modify.

patch_json

Required. The JSON document to meger and patch into the original JSON document.

Return value

If both parameters are JSON objects, the SQLite json_patch() function merges and patchs the JSON object patch_json to the original JSON object original_json, and returns the patched original JSON object.

Otherwise, the SQLite json_patch() function returns the second parameter patch_json.

Examples

Here are some examples to show the usages of json_patch().

Patch JSON objects

The following statement uses the json_patch() function to insert new members into the original JSON object:

SELECT json_patch('{"x": 1}', '{"y": 2}');

json_patch('{"x": 1}', '{"y": 2}')
----------------------------------
{"x":1,"y":2}

The following statement uses the json_patch() function to update the value of the member x in the original JSON object {"x": 1}:

SELECT json_patch('{"x": 1}', '{"x": 2}');

json_patch('{"x": 1}', '{"x": 2}')
----------------------------------
{"x":2}

The following statement uses the json_patch() function remove the member y from the original JSON object {"x":1,"y":2}:

SELECT json_patch('{"x":1,"y":2}', '{"y": null}');

json_patch('{"x":1,"y":2}', '{"y": null}')
------------------------------------------
{"x":1}

You can also add, update and delete a JSON object in a statement, for example:

SELECT json_patch('{"x":1,"y":2}', '{"x":0,"y": null,"z":3}');

json_patch('{"x":1,"y":2}', '{"x":0,"y": null,"z":3}')
------------------------------------------------------
{"x":0,"z":3}

non JSON objects

If the two parameters are not both JSON objects, the second parameter is returned, and the SQLite json_patch() function returns the second parameter patch_json. as follows:

• Provide two numbers to json_patch()

  SELECT json_patch(1, 2);
  

  json_patch(1, 2)
  ----------------
  2

• Provide a number and a string to json_patch()

  SELECT json_patch(1, '"a"');
  

  json_patch(1, '"a"')
  --------------------
  "a"

• Provide a number and a array to json_patch()

  SELECT json_patch(1, '[1,2]');
  

  json_patch(1, '[1,2]')
  ----------------------
  [1,2]

• Provide two arrays to json_patch()

  SELECT json_patch('[0]', '[1,2]');
  

  json_patch('[0]', '[1,2]')
  --------------------------
  [1,2]