blob: bdd9befa57352c436faed36543c59effa0e7960f [file] [view]
---
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
```sql
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
```sql
-- 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
* [SELECT](sql-ref-syntax-qry-select.html)
* [JSON_VALUE](sql-ref-syntax-qry-select-json-value.html)
* [JSON_TABLE](sql-ref-syntax-qry-select-json-table.html)
* [Built-in Functions](sql-ref-functions-builtin.html)