layout: global title: JSON_ARRAY displayTitle: JSON_ARRAY license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Description

The JSON_ARRAY constructor function builds a JSON array from a list of argument values and returns it as JSON text. This is the SQL-standard way (SQL:2016) to assemble a JSON array inline, and is commonly used to migrate queries from other systems such as Oracle, DB2, and MySQL. JSON_ARRAY is an expression that can appear anywhere a value is allowed.

Each argument is serialized with the same JSON writer as the built-in to_json function, so numbers, decimals, booleans, dates, timestamps, and nested structs/arrays/maps render the same way. Null-field handling inside a struct argument therefore follows spark.sql.jsonGenerator.ignoreNullFields, exactly as to_json does; the ON NULL clause below controls only the top-level array elements.

A call without any clause or FORMAT JSON, nested or not, resolves like an ordinary function call, so a same-named function placed before system.builtin in the SQL PATH, or a replacement injected by a session extension, can own it. Each nested call is resolved on its own, exactly like the outer one. Qualifying every level (e.g. builtin.json_array(builtin.json_array(1))) selects the built-in over a function in the PATH; adding a clause to every level (e.g. json_array(json_array(1 NULL ON NULL) NULL ON NULL)) always selects it, even over an extension's replacement.

Syntax

JSON_ARRAY ( [ value [ FORMAT JSON ] [, ...] ]
             [ { NULL | ABSENT } ON NULL ]
             [ RETURNING data_type ] )

Parameters

  • value

    An expression producing an element of the array. Arguments may have different types and may be nested JSON_ARRAY constructors. JSON_ARRAY() with no arguments produces the empty array [].

  • FORMAT JSON

    Marks a string value as already-JSON text, so it is spliced into the array verbatim instead of being quoted as a JSON string. For example, JSON_ARRAY('[1,2]') produces ["[1,2]"], while JSON_ARRAY('[1,2]' FORMAT JSON) produces [[1,2]]. A nested JSON_ARRAY constructor carries FORMAT JSON implicitly, so JSON_ARRAY(JSON_ARRAY(1)) and a qualified or otherwise routed call such as builtin.json_array(json_array(1)) both produce [[1]] when Spark‘s built-in owns every call (see above; an extension’s replacement can still own a qualified call). FORMAT JSON requires a string argument (an untyped NULL literal is also accepted and follows the ON NULL behavior, exactly as a non-FORMAT JSON NULL would). At runtime, a non-null FORMAT JSON value must contain exactly one well-formed JSON value; malformed text or multiple top-level values raise an error. This decision is fixed from the query text and does not depend on query optimization: a JSON_ARRAY result that reaches an argument through a column reference, including a lateral column alias, is a plain STRING and is quoted, whether or not the optimizer inlines it. The exception is a GROUP BY alias of a JSON producer, which stands for that producer and is spliced.

  • { NULL | ABSENT } ON NULL

    How to handle a NULL element:

    • ABSENT ON NULL (the default) omits NULL elements from the array.
    • NULL ON NULL keeps them as JSON null values.
  • RETURNING data_type

    The type of the result. It must be a string type; the result is JSON text. If RETURNING is omitted, the result type is STRING. CHAR / VARCHAR are normalized to STRING (the length is not enforced, because the fragment is serialized directly).

Examples

-- Construct an array from a mixed value list
SELECT json_array(1, 'x', true);
+---------------------------+
|json_array(1, x, true)     |
+---------------------------+
|[1,"x",true]               |
+---------------------------+

-- ABSENT ON NULL (the default) drops NULL elements
SELECT json_array(1, NULL, 3);
+------------------------+
|json_array(1, NULL, 3)  |
+------------------------+
|[1,3]                   |
+------------------------+

-- NULL ON NULL keeps them as JSON null
SELECT json_array(1, NULL, 3 NULL ON NULL);
+--------------------------------------+
|json_array(1, NULL, 3 NULL ON NULL)   |
+--------------------------------------+
|[1,null,3]                            |
+--------------------------------------+

-- A nested JSON_ARRAY is spliced in raw (implicit FORMAT JSON)
SELECT json_array(json_array(1, 2), 3);
+---------------------------------+
|json_array(json_array(1, 2), 3)  |
+---------------------------------+
|[[1,2],3]                        |
+---------------------------------+

-- FORMAT JSON splices an already-JSON string verbatim
SELECT json_array('[1,2]' FORMAT JSON);
+----------------------------------+
|json_array([1,2] FORMAT JSON)     |
+----------------------------------+
|[[1,2]]                           |
+----------------------------------+

Related Statements