SQL/JSON Function JSON

25.3 SQL/JSON Function JSON_OBJECT

SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.

It can accept any number of arguments, each of which is one of the following:

An explicit field name–value pair. Example: answer : 42.

A name–value pair argument specifies an object member for the generated JSON object (except when the value expression evaluates to SQL NULL and the ABSENT ON NULL clause applies). The name and value are SQL expressions. The name expression must evaluate to a SQL string. The value expression must evaluate to a SQL value that is of JSON data type or that can be rendered as a JSON value. The name and value expressions are separated by keyword VALUE or a colon (:).

Note:

Some client drivers might try to scan query text and identify bind variables before sending the query to the database. In some such cases a colon as name–value separator in json_object might be misinterpreted as introducing a bind variable. You can use keyword VALUE as the separator to avoid this problem ('Name' VALUE Diderot), or you can simply enclose the value part of the pair in parentheses: 'Name':(Diderot).
A relational column name, possibly preceded by a table name or alias, or a view name followed by a dot (.). Example: t1.address.

In this case, for a given row of data, the JSON-object member specified by the column-name argument has the column name as its field name and the column value as the field value.

Regardless of whether it is quoted, the column name you provide is interpreted case-sensitively. For example, if you use Email as a column-name argument then the data in column EMAIL is used to produce object members with field name Email (not EMAIL).
A table name or alias, or a view name, followed by a dot and an asterisk wildcard (.*). Example: t1.*. (The name or alias can also be prefixed by a database schema name, as in myschema.t1.*.)

In this case, all columns of the table or view are used as input. Each is handled as if it were named explicitly. In particular, the column names are interpreted case-sensitively.

Alternatively, json_object accepts a single argument that is one of the following:

An instance of a user-defined SQL object-type. Example: json_object(my_mailing_address_type).

In this case, the resulting JSON-object field names are taken from the SQL object attribute names, and their values are taken from the SQL object attribute values (to which JSON generation is applied recursively).

You can use keywords WITH TYPENAME following the object-type instance argument. This causes the resulting object to also contain a member with field type, whose value is a string naming the user-defined type. Example 25-7 illustrates this.
An asterisk wildcard (*). Example: json_object(*).

The wildcard acts as a shortcut to explicitly specifying all of the columns of a table or view, to produce the object members. The resulting JSON-object field names are the uppercase column names. You can use a wildcard with a table, a view, or a table alias, which is understood from the FROM list. The columns can be of any SQL data type.

Note the difference between this case (json_object(*)) and the case described above, where the asterisk is preceded by an explicit table or view name (or table alias), followed by a dot: json_object(t.*). In the json_object(*) case, the column names are not interpreted case-sensitively.

Another way of describing the use of asterisk wildcards with json_object is to say that it follows what is allowed for wildcards in a SQL SELECT list.

Just as for SQL/JSON condition is json, you can use keywords STRICT and WITH UNIQUE KEYS with functions json_object and json_objectagg. The behavior for each is the same as for is json.

Example 25-2 Using Name–Value Pairs with JSON_OBJECT

This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR) whose salary is greater than 15000.

It passes explicit name–value pairs to specify the members of the JSON object. The object includes, as the value of its field contactInfo, an object with fields mail and phone.

The use of RETURNING JSON here specifies that the JSON data is returned as JSON data type, not the default return type, VARCHAR2(4000).

SELECT json_object('id'          : employee_id,
                   'name'        : first_name || ' ' || last_name,
                   'contactInfo' : json_object('mail'  : email,
                                               'phone' : phone_number),
                   'hireDate'    : hire_date,
                   'pay'         : salary
                   RETURNING JSON) 
  FROM hr.employees
  WHERE salary > 15000;

The query returns rows such as this (pretty-printed here for clarity):

{"id"          : 101,
 "name"        : "Neena Kochhar",
 "contactInfo" : {"mail"  : "NKOCHHAR",
                  "phone" : "515.123.4568"},
 "hireDate"    : "21-SEP-05",
 "pay"         : 17000}

Note:

Because function json_object always returns JSON data, there is no need to specify FORMAT JSON for the value of input field contactInfo. But if the value of that field had been given as, for example, '{"mail":' || email ', "phone":' || phone_number || '}' then you would need to follow it with FORMAT JSON to have that string value interpreted as JSON data:

"contactInfo" :
  '{"mail":' || email ', "phone":' || phone_number || '}'
FORMAT JSON,

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { 'id'          : employee_id,
              'name'        : first_name || ' ' || last_name,
              'contactInfo' : JSON { 'mail'  : email,
                                     'phone' : phone_number }
              'hireDate'    : hire_date,
              'pay'         : salary } 
  FROM hr.employees
  WHERE salary > 15000;

Example 25-3 Using Column Names with JSON_OBJECT

This example constructs a JSON object for the employee whose employee_id is 101. The fields produced are named after the columns, but case-sensitively.

The use of RETURNING JSON here specifies that the JSON data is returned as JSON data type, not the default return type, VARCHAR2(4000).

SELECT json_object(last_name,
                   'contactInfo' : json_object(email, phone_number),
                   hire_date,
                   salary,
                   RETURNING JSON)
  FROM hr.employees
  WHERE employee_id = 101;

The query returns rows such as this (pretty-printed here for clarity):

{"last_name"   : "Kochhar",
 "contactInfo" : {"email"        : "NKOCHHAR",
                  "phone_number" : "515.123.4568"},
 "hire-date"   : "21-SEP-05",
 "salary"      : 17000}

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { last_name,
              'contactInfo' : JSON { email, phone_number },
              hire_date,
              salary}
  FROM hr.employees
  WHERE employee_id = 101;

Example 25-4 Using a Wildcard (*) with JSON_OBJECT

This example constructs a JSON object for each employee whose salary is greater than 15000. Each column of table employees is used to construct one object member, whose field name is the (uppercase) column name. Note that a SQL NULL value results in a JSON field value of null.

The use of RETURNING JSON here specifies that the JSON data is returned as JSON data type, not the default return type, VARCHAR2(4000).

SELECT json_object(* RETURNING JSON)
  FROM hr.employees
  WHERE salary > 15000;

The query returns rows such as this (pretty-printed here for clarity):

JSON_OBJECT(*)
--------------
{"EMPLOYEE_ID":100,
 "FIRST_NAME":"Steven",
 "LAST_NAME":"King",
 "EMAIL":"SKING",
 "PHONE_NUMBER":"515.123.4567",
 "HIRE_DATE":"2003-06-17T00:00:00",
 "JOB_ID":"AD_PRES",
 "SALARY":24000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":null,
 "DEPARTMENT_ID":90}

{"EMPLOYEE_ID":101,
 "FIRST_NAME":"Neena",
 "LAST_NAME":"Kochhar",
 "EMAIL":"NKOCHHAR",
 "PHONE_NUMBER":"515.123.4568",
 "HIRE_DATE":"2005-09-21T00:00:00",
 "JOB_ID":"AD_VP",
 "SALARY":17000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":100,
 "DEPARTMENT_ID":90}

{"EMPLOYEE_ID":102,
 "FIRST_NAME":"Lex",
 "LAST_NAME":"De Haan",
 "EMAIL":"LDEHAAN",
 "PHONE_NUMBER":"515.123.4569",
 "HIRE_DATE":"2001-01-13T00:00:00",
 "JOB_ID":"AD_VP",
 "SALARY":17000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":100,
 "DEPARTMENT_ID":90}

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { * }
  FROM hr.employees
  WHERE salary > 15000;

Example 25-5 Using JSON_OBJECT With ABSENT ON NULL

This example queries table hr.locations from standard database schema HR to create JSON objects with fields city and province.

The default NULL-handling behavior for json_object is NULL ON NULL.

In order to prevent the creation of a field with a null JSON value, this example uses ABSENT ON NULL. The NULL SQL value for column state_province when column city has value 'Singapore' means that no province field is created for that location.

SELECT JSON_OBJECT('city'     : city,
                   'province' : state_province ABSENT ON NULL) 
  FROM hr.locations
  WHERE city LIKE 'S%';

Here is the query output:

JSON_OBJECT('CITY'ISCITY,'PROVINCE'ISSTATE_PROVINCEABSENTONNULL)
----------------------------------------------------------------
{"city":"Southlake","province":"Texas"}
{"city":"South San Francisco","province":"California"}
{"city":"South Brunswick","province":"New Jersey"}
{"city":"Seattle","province":"Washington"}
{"city":"Sydney","province":"New South Wales"}
{"city":"Singapore"}
{"city":"Stretford","province":"Manchester"}
{"city":"Sao Paulo","province":"Sao Paulo"}

Because there is no RETURNING clause in this example, the JSON data is returned as VARCHAR2(4000), the default. If RETURNING JSON were used then you could use this alternative syntax for the query:

SELECT JSON {'city'     : city,
             'province' : state_province ABSENT ON NULL} 
  FROM hr.locations
  WHERE city LIKE 'S%';

Example 25-6 Using a User-Defined Object-Type Instance with JSON_OBJECT

This example creates table po_ship with column shipping of object type shipping_t. (It uses SQL/JSON function json_value to construct the shipping_t instances from JSON data — see Example 20-5.)

It then uses json_object to generate JSON objects from the SQL object-type instances in column po_ship.shipping, returning them as JSON data type instances.

CREATE TABLE po_ship
  AS SELECT json_value(data, '$.ShippingInstructions'
                       RETURNING shipping_t)
  shipping
  FROM j_purchaseorder;

DESCRIBE po_ship;

Name      Null?   Type
--------- ------- ----------
SHIPPING          SHIPPING_T

SELECT json_object(shipping RETURNING JSON)
  FROM po_ship;

This is the query output (pretty-printed here, for clarity).

JSON_OBJECT(SHIPPING)
---------------------
{"NAME":"Alexis Bull",
 "ADDRESS":{"STREET":"200 Sporting Green",
            "CITY":"South San Francisco"}}
{"NAME":"Sarah Bell",
 "ADDRESS":{"STREET":"200 Sporting Green",
            "CITY":"South San Francisco"}}

Because the return type from json_object is JSON, this is an alternative syntax for the same query:

SELECT JSON {shipping} FROM po_ship;

Example 25-7 Using WITH TYPENAME with JSON_OBJECT

This example shows the effect of using keywords WITH TYPENAME after a user-defined object argument: field type is included, with value a string naming the user-defined object type from which the JSON object was generated.

The example defines object type my_mailing_address_type, then creates a table with a column of that type and inserts a row with such an object into the table. The example assumes that the object type is created by database user (schema) user_1.

Two queries then use function json_object to generate a JSON object from the user-defined object in the table column. The second query is the same as the first, but it uses keywords WITH TYPENAME, causing the resulting object to include a member with string-valued field type. The string value is "USER_1.MY_MAILING_ADDRESS_TYPE" (showing that the type is defined and owned by schema user_1). Query output is shown here pretty-printed, for clarity.

CREATE OR REPLACE TYPE my_mailing_address_type
  AS OBJECT(Street VARCHAR2(80),
            City   VARCHAR2(80),
            State  CHAR(2),
            Zip    VARCHAR2(10));

CREATE TABLE t1 (col1 my_mailing_address_type);

INSERT INTO t1 VALUES (my_mailing_address_type('street1', 'city1', 'CA',
    '12345'));

SELECT json_object(col1) FROM t1;

JSON_OBJECT(COL1)
-----------------
{"STREET" : "street1",
 "CITY"   : "city1",
 "STATE"  : "CA",
 "ZIP"    : "12345"}

SELECT json_object(col1 WITH TYPENAME) FROM t1;

JSON_OBJECT(COL1WITHTYPENAME)
-----------------------------
{"type"   : "USER_1.MY_MAILING_ADDRESS_TYPE",
 "STREET" : "street1",
 "CITY"   : "city1",
 "STATE"  : "CA",
 "ZIP"    : "12345"}