MaxCompute:JSON_INSERT

Syntax

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

Parameters

• input: required. This parameter specifies the JSON data into which you want to insert a JSON value. The parameter value can be of the JSON ARRAY or JSON OBJECT type.

• path: required. This parameter specifies the position at which you want to insert a JSON value.

• value: required. This parameter specifies the JSON value that you want to insert.

• insert_after: required.

  • Valid values if the input parameter contains an array and the value specified by the path parameter exists in the array:

    • true: The specified JSON value is inserted after the specified position.

    • false: The specified JSON value is inserted before the specified position. This is the default value.

  Note

  If the input parameter contains an array, the JSON value is inserted into the array based on the following rules. In the rules, n indicates the array length, and m indicates the position at which you want to insert the JSON value.

  • If m ≥ n, the JSON value is appended to the array.

  • If m < -n, the JSON value is prepended to the array.

  • If -n ≤ m < n, the JSON value is inserted at the position based on the value of insert_after.

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

Return value

A modified JSON file is returned.

Examples

• Example 1: Insert a JSON value at the 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 JSON value before a specific value 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 JSON value after a specific value 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 JSON value into an array. If the value of the path parameter is greater than the array length, the JSON value is appended to the 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: Insert a JSON value into an array. If the value of the path parameter is less than the negative value of the array length, the JSON value is prepended to 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: Insert a JSON value into an array. If the value of the path parameter is greater than or equal to the negative value of the array length and less than the array length, the JSON value is inserted at the specified position.

  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]} |
  +-----+