blob: 7b4d3ac54ffebcbad68156460340543dad74940a [file] [view]
---
layout: global
title: JSON_OBJECT
displayTitle: JSON_OBJECT
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_OBJECT` constructor function builds a JSON object from key/value pairs and returns it as
JSON text. This is the SQL-standard way (SQL:2016) to assemble a JSON object inline, with common
compatibility syntax from other systems. `JSON_OBJECT` is an expression that can appear anywhere a
value is allowed.
Each value 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 value therefore follows
`spark.sql.jsonGenerator.ignoreNullFields`, exactly as `to_json` does; the `ON NULL` clause below
controls only the top-level object members.
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_object('a', builtin.json_object('b', 1))`)
selects the built-in over a function in the `PATH`; adding a clause to every level
(e.g. `json_object('a' VALUE json_object('b' VALUE 1 NULL ON NULL) NULL ON NULL)`) always selects
it, even over an extension's replacement.
This is an initial subset of the SQL:2016 `JSON_OBJECT` constructor. It supports the key/value
members, the value-level `FORMAT JSON` marker, the `{ NULL | ABSENT } ON NULL` clause, and a
string-type `RETURNING`. The following SQL/JSON clauses are not yet supported:
* The `{ WITH | WITHOUT } UNIQUE KEYS` clause. Duplicate keys are kept in source order (the
`WITHOUT UNIQUE KEYS` behavior); there is no option to reject them.
* `RETURNING` to a non-string type, or with a binary/`FORMAT JSON` output clause. The result is
always JSON text of a string type.
### Syntax
```sql
JSON_OBJECT ( [ { key VALUE value | KEY key VALUE value | key : value } [ FORMAT JSON ] [, ...] ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type ] )
JSON_OBJECT ( [ key, value [, key, value] ... ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type ] )
```
### Parameters
* **key**
An expression producing a member name. It must be a non-null string; a `NULL` key raises an
error at runtime. The key is validated before the `ON NULL` handling, so a `NULL` key raises the
error even when the member's value is `NULL` and `ABSENT ON NULL` would otherwise omit it.
In the `key : value` form, the first top-level `:` after the key ends it, so a `:` extraction
key must be parenthesized and cast to a string (an extraction yields a `VARIANT`):
`JSON_OBJECT((v:x::string) : 1)`, or use the `VALUE` form `JSON_OBJECT(v:x::string VALUE 1)`.
* **value**
An expression producing the member value. Values may have different types and may be nested
JSON constructors. `JSON_OBJECT()` with no members produces the empty object `{}`. A value is
spliced in as raw JSON, rather than quoted as a string, only when it carries `FORMAT JSON` (see
below) -- either the explicit clause or the implicit form carried by a recognized JSON producer
written directly in the value position. This covers a nested `JSON_OBJECT` or `JSON_ARRAY` (e.g.
`JSON_OBJECT('a' VALUE JSON_OBJECT('b' VALUE 1))` produces `{"a":{"b":1}}`), and a nested
`JSON_QUERY` under the default `KEEP QUOTES`, which returns JSON text (e.g.
`JSON_OBJECT('a' VALUE JSON_QUERY('{"o":{"x":1}}', '$.o'))` produces `{"a":{"x":1}}`). A
`JSON_QUERY` with `OMIT QUOTES` returns an ordinary string, so it is quoted like any other value
(e.g. `JSON_OBJECT('n' VALUE JSON_QUERY('{"n":"Ada"}', '$.n' OMIT QUOTES))` produces
`{"n":"Ada"}`). As an exception, a `GROUP BY` alias of a JSON producer stands for that producer
and is spliced; other column references, including a lateral column alias, are quoted. Without
`FORMAT JSON`, an ordinary string value is always quoted, even when its contents are themselves
valid JSON text (e.g. `JSON_OBJECT('a' VALUE '{"b":1}')` produces `{"a":"{\"b\":1}"}`).
Implicit raw splicing applies to the direct syntax above, to the
unqualified comma form (e.g. `json_object('a', json_object('b', 1))`), and to a qualified or
otherwise generic routed call (e.g. `builtin.json_object('a', json_object('b', 1))`); each
produces `{"a":{"b":1}}` when Spark's built-in owns every call (see above; an extension's
replacement can still own a qualified call).
* **FORMAT JSON**
Marks a string `value` as already-JSON text, so it is spliced into the object verbatim instead
of being quoted (e.g. `JSON_OBJECT('a' VALUE '{"b":1}' FORMAT JSON)` produces `{"a":{"b":1}}`).
A nested JSON constructor written directly in the value position carries `FORMAT JSON`
implicitly. `FORMAT JSON` requires a string argument (an untyped `NULL` literal is also accepted
and follows the `ON NULL` behavior). 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.
* **{ NULL | ABSENT } ON NULL**
How to handle a member whose `value` is `NULL`:
* `NULL ON NULL` (the default) keeps the member with a JSON `null` value.
* `ABSENT ON NULL` omits the member entirely.
* **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 object from key/value pairs
SELECT json_object('id' VALUE 7, 'name' VALUE 'Ada') AS obj;
+---------------------+
|obj |
+---------------------+
|{"id":7,"name":"Ada"}|
+---------------------+
-- KEY before the member name is also accepted
SELECT json_object(KEY 'id' VALUE 7, KEY 'name' VALUE 'Ada') AS obj;
+---------------------+
|obj |
+---------------------+
|{"id":7,"name":"Ada"}|
+---------------------+
-- The `:` separator is equivalent to VALUE; NULL ON NULL (the default) keeps null members
SELECT json_object('id' : 7, 'v' : NULL) AS obj;
+-----------------+
|obj |
+-----------------+
|{"id":7,"v":null}|
+-----------------+
-- Comma-separated key/value pairs are also accepted
SELECT json_object('id', 7, 'name', 'Ada') AS obj;
+---------------------+
|obj |
+---------------------+
|{"id":7,"name":"Ada"}|
+---------------------+
-- ABSENT ON NULL omits members whose value is NULL
SELECT json_object('id' : 7, 'v' : NULL ABSENT ON NULL) AS obj;
+--------+
|obj |
+--------+
|{"id":7}|
+--------+
-- A nested JSON_OBJECT composes and is spliced in raw
SELECT json_object('a' VALUE json_object('b' VALUE 1)) AS obj;
+-------------+
|obj |
+-------------+
|{"a":{"b":1}}|
+-------------+
-- FORMAT JSON splices an already-JSON string verbatim instead of quoting it
SELECT json_object('a' VALUE '{"b":1}' FORMAT JSON) AS obj;
+-------------+
|obj |
+-------------+
|{"a":{"b":1}}|
+-------------+
```
### 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)