JSON_STRIP_NULLS - MaxCompute - Alibaba Cloud Documentation Center

Removes null values from a JSON object or JSON array. Top-level JSON null values (JSON 'null') are never removed.

Syntax

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

Usage notes

JSON null (the value null in a JSON document) is distinct from SQL NULL (the absence of a value). This function targets JSON null values — not SQL NULL inputs. Passing SQL NULL as json_expr, include_arrays, or remove_empty causes the function to return SQL NULL rather than performing any removal.

Parameters

Parameter	Required	Type	Description
`json_expr`	Yes	JSON	A JSON object or JSON array. For more information, see JSON data type.
`include_arrays`	No	BOOLEAN	Whether to remove JSON null values from JSON arrays. Default: `TRUE`. Set to `FALSE` to keep null values in arrays.
`remove_empty`	No	BOOLEAN	Whether to remove empty JSON objects (and, when `include_arrays` is `TRUE` or omitted, empty JSON arrays) after null values are removed. Default: `FALSE`.
`json_path`	No	STRING	A JSON path that scopes null removal to a specific location within `json_expr`. When specified, you must provide all four input parameters and `json_path` must be the fourth argument.

Return value

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

Null-valued key-value pairs in JSON objects are removed.
If the result is an empty JSON object or array, the function returns JSON null.
If json_path is NULL or invalid, json_expr is returned unchanged.
If json_expr, include_arrays, or remove_empty is SQL NULL, NULL is returned.

Examples

Remove null values from JSON objects and arrays

The following examples show how include_arrays and remove_empty control the scope of null removal.

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;

Output:

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

Key behaviors:

json_data1: SQL NULL input returns SQL NULL.
json_data2: A bare JSON null (JSON 'null') is never removed — the function returns JSON null.
json_data3: With default include_arrays=TRUE, null elements are removed from the array.
json_data4: include_arrays=FALSE keeps null elements in the array.
json_data5: include_arrays=TRUE, remove_empty=TRUE removes null elements, then removes the resulting empty nested array.
json_data6: include_arrays=NULL (SQL NULL) causes the function to return SQL NULL.
json_data7: include_arrays=FALSE, remove_empty=TRUE removes nulls from objects only. "d":[null] and "e":[] are kept because include_arrays=FALSE.
json_data8: include_arrays=TRUE, remove_empty=TRUE removes all nulls and all resulting empty containers, leaving only {"f":1}.

Remove null values at a specific JSON path

Use json_path to scope null removal to one part of the document. The rest of the document is unchanged.

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;

Output:

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

Only $.person.address is cleaned: street and zip are removed. phone and company.location — outside the specified path — are unchanged.

Invalid json_path returns json_expr unchanged

If json_path is invalid, the function returns json_expr without modification.

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;

Output:

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

$person.a is not a valid JSON path (missing the . after $), so the input is returned as-is.

Related functions

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