| --- | |
| 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) |