JSON_SET

Syntax

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

-- Standard example.
-- Returns [{"f1":4,"f2":null},2,null,3].
SELECT JSON_SET(JSON'[{"f1":1,"f2":null}, 2, null, 3]',
  ARRAY("0", "f1"), 
  JSON'4', 
  false);

Parameters

• input: Required. The JSON content in which a value is replaced. JSON OBJECT and JSON ARRAY are supported.

• path: Required. The position of the JSON content to replace. The value is of the ARRAY<STRING> type. Each element in the array specifies the path value of one nesting level in the JSON content. If the JSON content has n nesting levels, the array contains n elements. If the JSON object is not nested, the first value is "0".

• value: Required. The JSON value used for the replacement.

• create_if_missing: Required. Specifies whether to add the JSON value to input if no value exists at the specified position. The value is of the BOOLEAN type. true specifies that the value is added, and false specifies that the value is not added.

Return value

The modified JSON content.

Examples

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

  -- Returns [{"f1":[2,3,4],"f2":null},2,null,3].
  SELECT JSON_SET(JSON'[{"f1":1,"f2":null},2,null,3]',
    ARRAY("0","f1"), 
    JSON'[2,3,4]', 
    false);
   
  -- JSON content with a two-level nested array.
  -- Returns [{"f1":1,"f2":null},2,3,[4,6]].
  SELECT JSON_SET(JSON'[{"f1":1,"f2":null},2,3,[4,5]]',
    ARRAY("3","1"), 
    JSON'6', 
    false);

• Example 2: Replace the value at a specified position in JSON content. create_if_missing is set to false. If no value exists at the specified position, no replacement or addition is performed.

  -- Returns [{"f1":1,"f2":null},2,null,3].
  SELECT JSON_SET(JSON'[{"f1":1,"f2":null},2,null,3]', 
    ARRAY("0","f3"), 
    JSON'[2,3,4]', 
    false);

• Example 3: Replace the value at a specified position in JSON content. create_if_missing is set to true. If no value exists at the specified position, the JSON content used for the replacement is added to input.

  -- Returns [{"f1":1,"f2":null,"f3":[2,3,4]},2,null,3].
  SELECT JSON_SET(JSON'[{"f1":1,"f2":null},2,null,3]', 
    ARRAY("0","f3"), 
    JSON'[2,3,4]', 
    true);

• Example 4: If input is a JSON OBJECT, the first element of path must be a valid key.

  -- Returns {"f1":[2,3,4]}
  
  SELECT JSON_SET(JSON'{"f1":1}', 
   ARRAY("f1"), 
   JSON'[2,3,4]', 
   false);
  
  
  -- Returns {"f1":1}
  
  -- The JSON content is not an array. In this case, the path ["0"] refers to the field whose key is the string "0". The path does not exist, so no replacement is performed.
  
  SELECT JSON_SET(JSON'{"f1":1}', 
   ARRAY("0"), 
   JSON'[2,3,4]', 
   false);

  Note

  If you run the command on a MaxCompute client and use_instance_tunnel is set to true, the results displayed in odpscmd and Logview may be inconsistent. Use the result from Logview.

Related functions

JSON_SET is a JSON function. For more information about JSON functions, see JSON functions.