You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/03/30 02:19:43 UTC
[calcite] 04/04: Site: Improve documentation for MySQL-specific
JSON operators
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 9bce62c6b6abd13d551d0eed1f1e14b024bbb2ae
Author: Hongze Zhang <ho...@apache.org>
AuthorDate: Mon Mar 11 15:46:08 2019 +0800
Site: Improve documentation for MySQL-specific JSON operators
Also, simplify a bit on type handling logic for JSON_TYPE function.
---
.../org/apache/calcite/runtime/SqlFunctions.java | 6 +-
site/_docs/reference.md | 130 ++++++++++++++-------
2 files changed, 90 insertions(+), 46 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index c508ea0..2ab9a3a 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2756,13 +2756,9 @@ public class SqlFunctions {
} else if (o == null) {
result = "NULL";
} else {
- result = "unknown";
- }
- if (result.equals("unknown")) {
throw RESOURCE.invalidInputForJsonType(o.toString()).ex();
- } else {
- return result;
}
+ return result;
} catch (Exception ex) {
throw RESOURCE.invalidInputForJsonType(o.toString()).ex();
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index a322b48..c1f6861 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1294,7 +1294,7 @@ Not implemented:
| Operator syntax | Description
|:--------------- |:-----------
-| ELEMENT(value) | Returns the sole element of a array or multiset; null if the collection is empty; throws if it has more than one element.
+| ELEMENT(value) | Returns the sole element of an array or multiset; null if the collection is empty; throws if it has more than one element.
| CARDINALITY(value) | Returns the number of elements in an array or multiset.
| value MEMBER OF multiset | Returns whether the *value* is a member of *multiset*.
| multiset IS A SET | Whether *multiset* is a set (has no duplicates).
@@ -1851,7 +1851,7 @@ The following functions modify 3D geometries.
Not implemented:
* ST_AddZ(geom, zToAdd) Adds *zToAdd* to the z-coordinate of *geom*
-* ST_Interpolate3DLine(geom) Returns *geom* with a interpolation of z values, or null if it is not a line-string or MULTILINESTRING
+* ST_Interpolate3DLine(geom) Returns *geom* with an interpolation of z values, or null if it is not a line-string or MULTILINESTRING
* ST_MultiplyZ(geom, zFactor) Returns *geom* with its z-values multiplied by *zFactor*
* ST_Reverse3DLine(geom [, sortOrder ]) Potentially reverses *geom* according to the z-values of its first and last coordinates
* ST_UpdateZ(geom, newZ [, updateCondition ]) Updates the z-values of *geom*
@@ -1940,24 +1940,32 @@ Not implemented:
### JSON Functions
+In the following:
+
+* *jsonValue* is a character string containing a JSON value;
+* *path* is a character string containing a JSON path expression; mode flag `strict` or `lax` should be specified in the beginning of *path*.
+
#### Query Functions
| Operator syntax | Description
|:---------------------- |:-----------
-| JSON_EXISTS(value, path [ { TRUE | FALSE | UNKNOWN | ERROR ) ON ERROR } ) | Test whether a JSON **value** satisfies a search criterion described using JSON path expression **path**
-| JSON_VALUE(value, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] ) | Extract an SQL scalar from a JSON **value** using JSON path expression **path**
-| JSON_QUERY(value, path [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] ) | Extract an JSON object or an JSON array from a JSON **value** using JSON path expression **path**
+| JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR ) ON ERROR } ) | Whether a *jsonValue* satisfies a search criterion described using JSON path expression *path*
+| JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] ) | Extract an SQL scalar from a *jsonValue* using JSON path expression *path*
+| JSON_QUERY(jsonValue, path [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] ) | Extract a JSON object or JSON array from *jsonValue* using the *path* JSON path expression
Note:
-* The common structure `value, path` is JSON API common syntax. **value** is a character string type json input, and **path** is a JSON path expression (in character string type too), mode flag **strict** or **lax** should be specified in the beginning of **path**.
-* **ON ERROR** clause, and **ON EMPTY** clause define the fallback behavior of the function when an error is thrown or a null value is about to be returned.
-* **ARRAY WRAPPER** clause defines how to represent JSON array result in JSON_QUERY function. Following is a comparision to demonstrate the difference among different wrapper behaviors.
+* The `ON ERROR` and `ON EMPTY` clauses define the fallback
+ behavior of the function when an error is thrown or a null value
+ is about to be returned.
+* The `ARRAY WRAPPER` clause defines how to represent a JSON array result
+ in `JSON_QUERY` function. The following examples compare the wrapper
+ behaviors.
Example Data:
```JSON
-{ "a": "[1,2]", "b": [1,2], "c": "hi"}
+{"a": "[1,2]", "b": [1,2], "c": "hi"}
```
Comparison:
@@ -1977,42 +1985,73 @@ Not implemented:
| Operator syntax | Description
|:---------------------- |:-----------
-| JSON_OBJECT( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct json object using a series of key (**name**) value (**value**) pairs
-| JSON_OBJECTAGG( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct json object using a key (**name**) value (**value**) pair
-| JSON_ARRAY( { value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct json array using a series of values (**value**)
-| JSON_ARRAYAGG( value [ FORMAT JSON ] [ ORDER BY orderItem [, orderItem ]* ] [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct json array using a value (**value**)
+| JSON_OBJECT( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct JSON object using a series of key (*name*) value (*value*) pairs
+| JSON_OBJECTAGG( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct a JSON object using a key (*name*) value (*value*) pair
+| JSON_ARRAY( { value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct a JSON array using a series of values (*value*)
+| JSON_ARRAYAGG( value [ FORMAT JSON ] [ ORDER BY orderItem [, orderItem ]* ] [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct a JSON array using a value (*value*)
Note:
-* The flag **FORMAT JSON** indicates the value is formatted as JSON character string. When **FORMAT JSON** is used, value should be de-parse from JSON character string to SQL structured value.
-* **ON NULL** clause defines how the JSON output represents null value. The default null behavior of **JSON_OBJECT** and **JSON_OBJECTAGG** is *NULL ON NULL*, and for **JSON_ARRAY** and **JSON_ARRAYAGG** it is *ABSENT ON NULL*.
-* If **ORDER BY** clause is provided, **JSON_ARRAYAGG** will sort the input rows by the specified order before performing aggregation.
+* The flag `FORMAT JSON` indicates the value is formatted as JSON
+ character string. When `FORMAT JSON` is used, the value should be
+ de-parse from JSON character string to a SQL structured value.
+* `ON NULL` clause defines how the JSON output represents null
+ values. The default null behavior of `JSON_OBJECT` and
+ `JSON_OBJECTAGG` is `NULL ON NULL`, and for `JSON_ARRAY` and
+ `JSON_ARRAYAGG` it is `ABSENT ON NULL`.
+* If `ORDER BY` clause is provided, `JSON_ARRAYAGG` sorts the
+ input rows into the specified order before performing aggregation.
#### Comparison Operators
-| Operator syntax | Description
-|:------------------------------------------------- |:-----------
-| value IS JSON [ VALUE ] | Whether *value* is a json value, *value* is in character string type
-| value IS NOT JSON [ VALUE ] | Whether *value* is not a json value, *value* is in character string type
-| value IS JSON SCALAR | Whether *value* is a json scalar value, *value* is in character string type
-| value IS NOT JSON SCALAR | Whether *value* is not a json scalar value, *value* is in character string type
-| value IS JSON OBJECT | Whether *value* is a json object, *value* is in character string type
-| value IS NOT JSON OBJECT | Whether *value* is not a json object, *value* is in character string type
-| value IS JSON ARRAY | Whether *value* is a json array, *value* is in character string type
-| value IS NOT JSON ARRAY | Whether *value* is not a json array, *value* is in character string type
+| Operator syntax | Description
+|:--------------------------------- |:-----------
+| jsonValue IS JSON [ VALUE ] | Whether *jsonValue* is a JSON value
+| jsonValue IS NOT JSON [ VALUE ] | Whether *jsonValue* is not a JSON value
+| jsonValue IS JSON SCALAR | Whether *jsonValue* is a JSON scalar value
+| jsonValue IS NOT JSON SCALAR | Whether *jsonValue* is not a JSON scalar value
+| jsonValue IS JSON OBJECT | Whether *jsonValue* is a JSON object
+| jsonValue IS NOT JSON OBJECT | Whether *jsonValue* is not a JSON object
+| jsonValue IS JSON ARRAY | Whether *jsonValue* is a JSON array
+| jsonValue IS NOT JSON ARRAY | Whether *jsonValue* is not a JSON array
#### MySQL Specific Operators
-| Operator syntax | Description
-|:------------------------------------------------- |:-----------
-| JSON_TYPE(value) | Returns a string indicating the type of a JSON **value**. This can be an object, an array, or a scalar type
-| JSON_DEPTH(value) | Returns a integer indicating the depth of a JSON **value**. This can be an object, an array, or a scalar type
-| JSON_PRETTY(value) | Returns a pretty-printing of JSON **value**.
-| JSON_LENGTH(value) | Returns a integer indicating the length of a JSON **value**. This can be an object, an array, or a scalar type
+| Operator syntax | Description
+|:--------------------------------- |:-----------
+| JSON_TYPE(jsonValue) | Returns a string value indicating the type of a *jsonValue*
+| JSON_DEPTH(jsonValue) | Returns an integer value indicating the depth of a *jsonValue*
+| JSON_PRETTY(jsonValue) | Returns a pretty-printing of *jsonValue*
+| JSON_LENGTH(jsonValue [, path ]) | Returns a integer indicating the length of *jsonValue*
-* JSON_TYPE
+Note:
-Example SQL:
+* `JSON_TYPE` / `JSON_DEPTH` return null if the argument is null
+* `JSON_TYPE` / `JSON_DEPTH` / `JSON_PRETTY` throw error if the argument is not a valid JSON value
+* `JSON_TYPE` generally returns an upper-case string flag indicating the type of the JSON input. Currently supported supported type flags are:
+ * INTEGER
+ * STRING
+ * FLOAT
+ * DOUBLE
+ * LONG
+ * BOOLEAN
+ * DATE
+ * OBJECT
+ * ARRAY
+ * NULL
+* `JSON_DEPTH` defines a JSON value's depth as follows:
+ * An empty array, empty object, or scalar value has depth 1;
+ * A non-empty array containing only elements of depth 1 or non-empty object containing only member values of depth 1 has depth 2;
+ * Otherwise, a JSON document has depth greater than 2.
+* `JSON_LENGTH` defines a JSON value's length as follows:
+ * A scalar value has length 1;
+ * The length of array or object is the number of elements is contains.
+
+Usage Examples:
+
+##### JSON_TYPE example
+
+SQL
```SQL
SELECT JSON_TYPE(v) AS c1
@@ -2023,15 +2062,15 @@ FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v)
LIMIT 10;
```
-Result:
+Result
| c1 | c2 | c3 | c4 |
| ------ | ----- | ------- | ------- |
| OBJECT | ARRAY | INTEGER | BOOLEAN |
-* JSON_DEPTH
+##### JSON_DEPTH example
-Example SQL:
+SQL
```SQL
SELECT JSON_DEPTH(v) AS c1
@@ -2042,15 +2081,15 @@ FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v)
LIMIT 10;
```
-Result:
+Result
| c1 | c2 | c3 | c4 |
| ------ | ----- | ------- | ------- |
| 3 | 2 | 1 | 1 |
-* JSON_LENGTH
+##### JSON_LENGTH example
-Example SQL:
+SQL
```SQL
SELECT JSON_LENGTH(v) AS c1
@@ -2061,12 +2100,21 @@ FROM (VALUES ('{"a": [10, true]}')) AS t(v)
LIMIT 10;
```
-Result:
+Result
| c1 | c2 | c3 | c4 |
| ------ | ----- | ------- | ------- |
| 1 | 2 | 1 | 1 |
+Not implemented:
+
+* JSON_LENGTH
+* JSON_INSERT
+* JSON_SET
+* JSON_REPLACE
+* JSON_REMOVE
+* JSON_KEYS
+
## User-defined functions
Calcite is extensible. You can define each kind of function using user code.