JSON_INSERT

Command format

JSON json_insert(JSON <input>, ARRAY STRING <path>, JSON <value>, BOOLEAN <insert_after>);

Parameters

• input: Required. The JSON content to modify. This parameter supports JSON ARRAY and JSON OBJECT data types.

• path: Required. The path that specifies the position at which to insert the JSON content.

• value: Required. The JSON content to insert.

• insert_after: Required.

  • If an input parameter contains an array and the path points to an existing element, this parameter specifies the insertion position:

    • true: Inserts the new value after the specified element.

    • false (default): Inserts the new value before the specified element.

  Note

  If the `input` parameter is an array, the following rules apply when you insert JSON content. In these rules, `n` is the length of the array and `m` is the specified insertion position:

  • If m >= n, the value is added to the end of the array.

  • If m < -n, the value is added to the beginning of the array.

  • If -n <= m < n, the position of the new value is determined by the insert_after parameter.

  • If the input parameter is not an array, the value is inserted at the end by default.

Return value

The modified JSON.

Examples

• Example 1: Insert a new value at a specified position.

  select json_insert(JSON'{"f1":1,"f2":{"t1":1,"t2":2}}', 
                     array("f2","t3"), 
                     JSON'3',
                     false
                    );

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":1,"f2":{"t1":1,"t2":2,"t3":3}} |
  +-----+

• Example 2: Insert a value before an element in an array.

  select json_insert(JSON'{"f1":[0,1,2]}', array("f1","0"), JSON'10', false);

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":[10,0,1,2]} |
  +-----+

• Example 3: Insert a value after an element in an array.

  select json_insert(JSON'{"f1":[0,1,2]}', array("f1","0"), JSON'10', true);

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":[0,10,1,2]} |
  +-----+

• Example 4: Insert a value at an index beyond the end of an array.

  select json_insert(JSON'{"f1":[0,1,2]}', array("f1","4"), JSON'4', false);

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":[0,1,2,4]} |
  +-----+

• Example 5: Inserting a value into a JSON array at an index less than the negative length of the array places the value at the beginning of the array.

  select json_insert(JSON'{"f1":[0,1,2]}', array("f1","-4"), JSON'4', false);

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":[4,0,1,2]} |
  +-----+

• Example 6: You can insert a value into an array at a specified position if the position is greater than or equal to the negative of the array length and less than the array length.

  select json_insert(JSON'{"f1":[0,1,2]}', array("f1","1"), JSON'10', false);

  The following result is returned.

  +-----+
  | _c0 |
  +-----+
  | {"f1":[0,10,1,2]} |
  +-----+