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

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

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