JSON_OBJECT

The JSON_OBJECT function evaluates a key-value pair and returns a JSON object containing the pair

Syntax

<JSON object constructor> ::= "JSON_OBJECT" “(" [ <JSON name and value> [ { "," <JSON name and value> }... ] [ "NULL ON NULL" | "ABSENT ON NULL" ]  ] ")"

<JSON name and value> ::= <JSON name> ":" <JSON value expression>

<JSON name> ::= <character value expression>

<JSON value expression> ::= <value expression>

Rules

1.

Return type is String;

2.

NULL ON NULL is implicit;

3.

If <JSON name and value> number is 0, then an empty JSON object “{}” will be return;

4.

Add option to control, whether to throw exception if name is null, or replace the name with empty string, or ignore null keys, should not throw exception by default.

5.

RFC7159 do not allow duplicate keys, in CIS, using configurations to avoid duplicate key or allow it.

6.

Besides numeric, boolean, null types, JSON values should be string or cast as string.

Examples

JSON_OBJECT is the same as the other common sql functions:

 

SELECT * from /shared/examples/ds_orders/tutorial/customers ORDER BY JSON_OBJECT()

SELECT a.customerid, a.CompanyName from /shared/examples/ds_orders/tutorial/customers a INNER JOIN  /shared/examples/ds_orders/shippingmethods b ON '{}'=JSON_OBJECT()