JSON_OBJECT

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.

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"}). 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 and to the unqualified comma form (e.g. json_object('a', json_object('b', 1)), which carries the nested producer through to the built-in and produces {"a":{"b":1}}). A qualified or otherwise generic routed call (e.g. builtin.json_object('a', json_object('b', 1))) currently quotes the nested result instead, producing {"a":"{\"b\":1}"}.

• 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

• SELECT
• JSON_VALUE
• JSON_TABLE
• Built-in Functions