Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/object_keys below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

OBJECT_KEYS | Snowflake Documentation

Returns an array containing the list of keys in the top-most level of the input object.

object

The value for which you want the keys. The input value must be one of the following:

• An OBJECT.

• A VARIANT that contains a value of type OBJECT.

The function returns an ARRAY containing the keys.

If object is a structured OBJECT, the function returns an ARRAY(VARCHAR).

• If the object contains nested objects (e.g. objects within objects), this returns only the keys from the top-most level.

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);

Copy

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))
    ;

Copy

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"                 |
| ]                    | ]                     |
+----------------------+-----------------------+

Copy

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"  |
| ]              |
+----------------+

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4