MaxCompute:JSON_SET

Syntax

JSON json_set(JSON <input>, ARRAY STRING <path>, JSON <value>, BOOLEAN <create_if_missing>);

Parameters

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

• path: required. This parameter specifies the position at which you want to replace a value. The value of this parameter is of the ARRAY<STRING> type. Each value in the array indicates a level of nested path. If a JSON file contains n levels of nested paths, the path parameter contains n values.

• value: required. This parameter specifies the JSON value that you want to use to replace a value.

• create_if_missing: required. This parameter specifies whether to add a value to the JSON file if no value at the specified position exists. Valid values:

  • true

  • false (default value)

Return value

A modified JSON file is returned.

Examples

• Example 1: Replace the value at the specified position in the JSON file.

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

  The following result is returned:

  +-----+
  | _c0 |
  +-----+
  | [{"f1":[2,3,4],"f2":null},2,null,3] |
  +-----+

• Example 2: Replace the value at the specified position in the JSON file. If the value does not exist, the replacement is not performed.

  select json_set(JSON'[{"f1":1,"f2":null},2,null,3]', 
                  array("0","f3"), 
                  JSON'[2,3,4]', 
                  false);

  The following result is returned:

  +-----+
  | _c0 |
  +-----+
  | [{"f1":1,"f2":null},2,null,3] |
  +-----+

• Example 3: Replace the value at the specified position in the JSON file. If the value does not exist, add a value.

  select json_set(JSON'[{"f1":1,"f2":null},2,null,3]', 
                  array("0","f3"), 
                  JSON'[2,3,4]', 
                  true);

  The following result is returned:

  +-----+
  | _c0 |
  +-----+
  | [{"f1":1,"f2":null,"f3":[2,3,4]},2,null,3] |
  +-----+