[CALCITE-2892] Add the JSON_KEYS function

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Forward Xu
• Reporter: Forward Xu
• Created: 05/Mar/19 05:55
• Updated: 24/Jun/19 15:03
• Resolved: 03/Apr/19 02:56

Description

JSON_KEYS({{json_doc[, path])}}

Returns the keys from the top-level value of a JSON object as a JSON array, or, if a path argument is given, the top-level keys from the selected path. Returns NULL if any argument is NULL, the json_doc argument is not an object, or path, if given, does not locate an object. An error occurs if the json_doc_argument is not a valid JSON document or the path_ argument is not a valid path expression or contains a {} or * wildcard.

The result array is empty if the selected object is empty. If the top-level value has nested subobjects, the return value does not include keys from those subobjects.

Example SQL:

// code placeholder
SELECT JSON_KEYS(v) AS c1
,JSON_KEYS(v, 'lax $.a') AS c2
,JSON_KEYS(v, '$.b') AS c3
,JSON_KEYS(v, 'strict $.a[0]') AS c4
,JSON_KEYS(v, 'strict $.a[1]') AS c5
FROM (VALUES ('{"a": [10, true],"b": {"c": 30}}')) AS t(v)
LIMIT 10;

Result:

c1	c2	c3	c4	c5
["a", "b"]	[NULL]	["c"]	[NULL]	[NULL]

Issue Links

• links to: GitHub Pull Request #1086