JSON_STRIP_NULLS - MaxCompute - Alibaba Cloud Documentation Center

Removes all fields or elements whose value is null from a JSON object or a JSON array.

Syntax

JSON JSON_STRIP_NULLS(
  JSON <json_expr>
  [, BOOLEAN <include_arrays> ]
  [, BOOLEAN <remove_empty> ]
  [, STRING <json_path> ]
)

Parameters

Parameter	Required	Description
json_expr	Yes	JSON type. A JSON object or a JSON array. For more information, see JSON data type.
include_arrays	No	BOOLEAN type. Specifies whether to delete JSON null values from JSON arrays. Valid values: If you do not specify this parameter or set this parameter to `TRUE` (default), the JSON null values are deleted. If you set this parameter to `FALSE`, the JSON null values are not deleted.
remove_empty	No	BOOLEAN type. Specifies whether to delete empty JSON objects after JSON null values are deleted. Valid values: If you do not specify this parameter or set this parameter to `FALSE` (default), the empty JSON objects are not deleted. A value of `TRUE` indicates deletion. Note If the value of remove_empty is `TRUE` and the value of include_arrays is `TRUE` or omitted, this function also deletes empty JSON arrays.
json_path	No	STRING type. For json_expr, this function removes JSON null values at the path specified by json_path. Note If you use the json_path parameter, you must specify four input parameters, and json_path must be the fourth input parameter.

Return value

Returns a value of the JSON type. The following rules apply:

If a field in a JSON object has a null value, the associated key-value pair is deleted.
If the resulting JSON object or array is empty, the function returns a JSON null.
If json_path is NULL or invalid, json_expr is returned.
If json_expr, include_arrays, or remove_empty is NULL, NULL is returned.

Examples

Example 1: Remove null values from a JSON object and a JSON array.

SELECT 
  JSON_STRIP_NULLS(NULL) AS json_data1,
  JSON_STRIP_NULLS(JSON 'null') AS json_data2,
  JSON_STRIP_NULLS(JSON '[1, null, 2, null]') AS json_data3,
  JSON_STRIP_NULLS(JSON '[1, null, 2, null]', FALSE) AS json_data4,
  JSON_STRIP_NULLS(JSON '[1, null, 2, null, [null]]',TRUE,TRUE) AS json_data5,
  JSON_STRIP_NULLS(JSON '[1, null, 2, null, [null]]',NULL,TRUE) AS json_data6,
  JSON_STRIP_NULLS(
    JSON '{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',
    FALSE,TRUE) AS json_data7,
  JSON_STRIP_NULLS(
    JSON '{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',
    TRUE,TRUE) AS json_data8;

The following result is returned:

+------------+------------+------------+-----------------+------------+------------+---------------------------+------------+
| json_data1 | json_data2 | json_data3 | json_data4      | json_data5 | json_data6 | json_data7                | json_data8 |
+------------+------------+------------+-----------------+------------+------------+---------------------------+------------+
| NULL       | null       | [1,2]      | [1,null,2,null] | [1,2]      | NULL       | {"d":[null],"e":[],"f":1} | {"f":1}    |
+------------+------------+------------+-----------------+------------+------------+---------------------------+------------+

Example 2: Remove empty objects at a specified JSON path.

SELECT JSON_STRIP_NULLS(
  JSON '{
    "person": {
        "name": "Alice", 
        "address": {
            "street": null, 
            "city": "New York",
            "zip": null
         }, 
         "phone": null
     }, 
     "company": {
       "name": "Acme Corp", 
       "location": null
       }
  }',
  true,
  true, 
  '$.person.address'
) AS json_data;

The following result is returned:

{"person":{"name":"Alice","phone":null,"address":{"city":"New York"}},"company":{"name":"Acme Corp","location":null}}

Example 3: If json_path is invalid, the function returns json_expr without performing any operations.

SELECT JSON_STRIP_NULLS(
  JSON '{
    "person": {
        "name": "Alice", 
        "address": {
            "street": null, 
            "city": "New York", 
            "zip": null
          }, 
          "phone": null
     }, 
     "company": {
        "name": "Acme Corp", 
        "location": null
      },
      "uid":null
  }',
  true,
  true, 
  '$person.a'
) AS json_data;

The following result is returned:

{"uid":null,"person":{"name":"Alice","phone":null,"address":{"zip":null,"city":"New York","street":null}},"company":{"name":"Acme Corp","location":null}}

Related functions

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