- Categories:
Semi-structured and structured data functions (Array/Object)
OBJECT_KEYS¶
Returns an array containing the list of keys in the top-most level of the input object.
Syntax¶
OBJECT_KEYS( <object> )
Arguments¶
Returns¶
The function returns an ARRAY containing the keys.
If object
is a structured OBJECT, the function returns an ARRAY(VARCHAR).
Usage notes¶
If the object contains nested objects (e.g. objects within objects), this returns only the keys from the top-most level.
Examples¶
Basic example¶
The next example shows OBJECT_KEYS working with both an OBJECT and a VARIANT that contains a value of type OBJECT.
Create a table that contains columns of types OBJECT and VARIANT.
CREATE TABLE objects_1 (id INTEGER, object1 OBJECT, variant1 VARIANT);INSERT values:
INSERT INTO objects_1 (id, object1, variant1) SELECT 1, OBJECT_CONSTRUCT('a', 1, 'b', 2, 'c', 3), TO_VARIANT(OBJECT_CONSTRUCT('a', 1, 'b', 2, 'c', 3)) ;Retrieve the keys from both the OBJECT and the VARIANT:
SELECT OBJECT_KEYS(object1), OBJECT_KEYS(variant1) FROM objects_1 ORDER BY id; +----------------------+-----------------------+ | OBJECT_KEYS(OBJECT1) | OBJECT_KEYS(VARIANT1) | |----------------------+-----------------------| | [ | [ | | "a", | "a", | | "b", | "b", | | "c" | "c" | | ] | ] | +----------------------+-----------------------+
Example of nested objects¶
This example shows that if the object contains nested objects, only the keys from the top-most level are returned.
SELECT OBJECT_KEYS ( PARSE_JSON ( '{ "level_1_A": { "level_2": "two" }, "level_1_B": "one" }' ) ) AS keys ORDER BY 1; +----------------+ | KEYS | |----------------| | [ | | "level_1_A", | | "level_1_B" | | ] | +----------------+