techdocsmith commented on code in PR #17635:
URL: https://github.com/apache/druid/pull/17635#discussion_r1931314426
##########
docs/querying/sql-functions.md:
##########
@@ -2787,70 +2787,222 @@ Returns the following:
## JSON_KEYS
-Returns an array of field names from `expr` at the specified `path`.
+Returns an array of field names from an expression, at a specified path.
-* **Syntax**: `JSON_KEYS(expr, path)`
+* **Syntax:** `JSON_KEYS(expr, path)`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example returns an array of field names from the nested column
`agent`:
+
+```sql
+SELECT
+ JSON_KEYS(agent, '$.') AS agent_keys
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `agent_keys` |
+| -- |
+| `[type, category, browser, browser_version, os, platform]` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_MERGE
-Merges two or more JSON `STRING` or `COMPLEX<json>` into one. Preserves the
rightmost value when there are key overlaps. Returning always a `COMPLEX<json>`
type.
+Merges two or more JSON `STRING` or `COMPLEX<json>` expressions into one,
preserving the rightmost value when there are key overlaps.
+The function always returns a `COMPLEX<json>` object.
* **Syntax:** `JSON_MERGE(expr1, expr2[, expr3 ...])`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example merges the `event` object with a static string
`example_string`:
+
+```sql
+SELECT
+ event,
+ JSON_MERGE(event, '{"example_string": 123}') as eventAndstring
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `event` | `eventAndstring` |
+| -- | -- |
+| `{"type":"PercentClear","percentage":55}` |
`{"type":"PercentClear","percentage":55,"example_string":123}` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_OBJECT
-Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types. The `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values. `JSON_OBJECT` can accept
colon-separated key-value pairs. The following syntax is equivalent:
`JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])`.
+Constructs a new `COMPLEX<json>` object from one or more expressions.
+The `KEY` expressions must evaluate to string types.
+The `VALUE` expressions can be composed of any input type, including other
`COMPLEX<json>` objects.
+The function can accept colon-separated key-value pairs.
-* **Syntax**: `JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4,
...])`
+* **Syntax:** `JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4,
...])`
+ or
+ `JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example creates a new object `combinedJSON` from `continent` in
`geo_ip` and `type` in `event`:
+
+```sql
+SELECT
+ JSON_OBJECT(
+ KEY 'geo_ip' VALUE JSON_QUERY(geo_ip, '$.continent'),
+ KEY 'event' VALUE JSON_QUERY(event, '$.type')
+ )
+ as combinedJSON
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `combinedJSON` |
+| -- |
+| `{"geo_ip": {"continent": "South America"},"event": {"type":
"PercentClear"}}` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_PATHS
-Returns an array of all paths which refer to literal values in `expr` in
JSONPath format.
+Returns an array of all paths which refer to literal values in an expression,
in JSONPath format.
-* **Syntax**: `JSON_PATHS(expr)`
+* **Syntax:** `JSON_PATHS(expr)`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example returns an array of distinct paths in the `geo_ip`
nested column:
+
+```sql
+SELECT
+ ARRAY_CONCAT_AGG(DISTINCT JSON_PATHS(geo_ip)) AS geo_ip_paths
+from "kttm_nested"
+```
+
+Returns the following:
+
+| `geo_ip_paths` |
+| -- |
+| `[$.city, $.continent, $.country, $.region]` |
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_QUERY
-Extracts a `COMPLEX<json>` value from `expr`, at the specified `path`.
+Extracts a `COMPLEX<json>` value from an expression at a specified path.
-* **Syntax**: `JSON_QUERY(expr, path)`
+* **Syntax:** `JSON_QUERY(expr, path)`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example returns the values of `percentage` in the `event` nested
column:
+
+```sql
+SELECT
+ "event",
+ JSON_QUERY("event", '$.percentage')
+FROM "kttm_nested"
+LIMIT 2
+```
+
+Returns the following:
Review Comment:
good example! I like how the event column shows how it's being "unnested" in
the percent column.
##########
docs/querying/sql-functions.md:
##########
@@ -2787,70 +2787,222 @@ Returns the following:
## JSON_KEYS
-Returns an array of field names from `expr` at the specified `path`.
+Returns an array of field names from an expression, at a specified path.
-* **Syntax**: `JSON_KEYS(expr, path)`
+* **Syntax:** `JSON_KEYS(expr, path)`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example returns an array of field names from the nested column
`agent`:
+
+```sql
+SELECT
+ JSON_KEYS(agent, '$.') AS agent_keys
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `agent_keys` |
+| -- |
+| `[type, category, browser, browser_version, os, platform]` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_MERGE
-Merges two or more JSON `STRING` or `COMPLEX<json>` into one. Preserves the
rightmost value when there are key overlaps. Returning always a `COMPLEX<json>`
type.
+Merges two or more JSON `STRING` or `COMPLEX<json>` expressions into one,
preserving the rightmost value when there are key overlaps.
+The function always returns a `COMPLEX<json>` object.
* **Syntax:** `JSON_MERGE(expr1, expr2[, expr3 ...])`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example merges the `event` object with a static string
`example_string`:
+
+```sql
+SELECT
+ event,
+ JSON_MERGE(event, '{"example_string": 123}') as eventAndstring
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `event` | `eventAndstring` |
+| -- | -- |
+| `{"type":"PercentClear","percentage":55}` |
`{"type":"PercentClear","percentage":55,"example_string":123}` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_OBJECT
-Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types. The `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values. `JSON_OBJECT` can accept
colon-separated key-value pairs. The following syntax is equivalent:
`JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])`.
+Constructs a new `COMPLEX<json>` object from one or more expressions.
+The `KEY` expressions must evaluate to string types.
+The `VALUE` expressions can be composed of any input type, including other
`COMPLEX<json>` objects.
+The function can accept colon-separated key-value pairs.
-* **Syntax**: `JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4,
...])`
+* **Syntax:** `JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4,
...])`
+ or
+ `JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example creates a new object `combinedJSON` from `continent` in
`geo_ip` and `type` in `event`:
+
+```sql
+SELECT
+ JSON_OBJECT(
+ KEY 'geo_ip' VALUE JSON_QUERY(geo_ip, '$.continent'),
+ KEY 'event' VALUE JSON_QUERY(event, '$.type')
+ )
+ as combinedJSON
Review Comment:
prefer underscore rather than camel case for alias: `combined_JSON`.
See line 2896.
##########
docs/querying/sql-functions.md:
##########
@@ -5158,11 +5347,47 @@ Returns the following:
## TRY_PARSE_JSON
-Parses `expr` into a `COMPLEX<json>` object. This operator deserializes JSON
values when processing them, translating stringified JSON into a nested
structure. If the input is not a `VARCHAR` or it is invalid JSON, this function
will result in a `NULL` value.
+Parses an expression into a `COMPLEX<json>` object.
+
+This function deserializes JSON values when processing them, translating
stringified JSON into a nested structure.
+If the input is invalid JSON or not a `VARCHAR`, it returns a `NULL` value.
-* **Syntax**: `TRY_PARSE_JSON(expr)`
+You can use this function instead of [PARSE_JSON](#parse_json) to insert a
null value when processing invalid data, instead of producing an error.
+
+* **Syntax:** `TRY_PARSE_JSON(expr)`
* **Function type:** JSON
+<details><summary>Example</summary>
+
+The following example creates a `COMPLEX<json>` object `gus` from a string of
fields:
+
+```sql
+SELECT
+ TRY_PARSE_JSON('{"name":"Gus","email":"[email protected]","type":"Pet"}')
as gus
+```
+
+Returns the following:
+
+| `gus` |
+| -- |
+| `{"name":"Gus","email":"[email protected]","type":"Pet"}` |
+
+
+The following example contains invalid data `x:x`:
Review Comment:
nice negative example
##########
docs/querying/sql-functions.md:
##########
@@ -2787,70 +2787,222 @@ Returns the following:
## JSON_KEYS
-Returns an array of field names from `expr` at the specified `path`.
+Returns an array of field names from an expression, at a specified path.
-* **Syntax**: `JSON_KEYS(expr, path)`
+* **Syntax:** `JSON_KEYS(expr, path)`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example returns an array of field names from the nested column
`agent`:
+
+```sql
+SELECT
+ JSON_KEYS(agent, '$.') AS agent_keys
+FROM "kttm_nested"
+LIMIT 1
+```
+
+Returns the following:
+| `agent_keys` |
+| -- |
+| `[type, category, browser, browser_version, os, platform]` |
+
+</details>
+
+[Learn more](sql-json-functions.md)
## JSON_MERGE
-Merges two or more JSON `STRING` or `COMPLEX<json>` into one. Preserves the
rightmost value when there are key overlaps. Returning always a `COMPLEX<json>`
type.
+Merges two or more JSON `STRING` or `COMPLEX<json>` expressions into one,
preserving the rightmost value when there are key overlaps.
+The function always returns a `COMPLEX<json>` object.
* **Syntax:** `JSON_MERGE(expr1, expr2[, expr3 ...])`
* **Function type:** JSON
-[Learn more](sql-json-functions.md)
+<details><summary>Example</summary>
+
+The following example merges the `event` object with a static string
`example_string`:
+
+```sql
+SELECT
+ event,
+ JSON_MERGE(event, '{"example_string": 123}') as eventAndstring
Review Comment:
suggest alias name with underscore `event_with_string` or similar. See line
2896.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
