从Snowflake JSON列检索值

CREATE OR REPLACE TABLE my_table (
    id INT,
    json_data VARIANT
);

INSERT INTO my_table (id, json_data) VALUES
(1, PARSE_JSON('{"name": "Alice", "age": 30, "address": {"city": "New York", "zip": "10001"}}')),
(2, PARSE_JSON('{"name": "Bob", "age": 25, "address": {"city": "San Francisco", "zip": "94105"}}'));

SELECT
    json_data:name AS name,
    json_data:age AS age
FROM my_table;

SELECT
    json_data:address.city AS city
FROM my_table;

SELECT
    json_data:get('name') AS name,
    json_data:get('age') AS age
FROM my_table;

SELECT
    json_data:get_path('address', 'city') AS city
FROM my_table;

CREATE OR REPLACE TABLE my_table (
    id INT,
    json_data VARIANT
);

INSERT INTO my_table (id, json_data) VALUES
(1, PARSE_JSON('{"name": "Alice", "hobbies": ["reading", "swimming", "hiking"]}')),
(2, PARSE_JSON('{"name": "Bob", "hobbies": ["gaming", "cycling"]}'));

SELECT
    id,
    json_data:name AS name,
    hobby.value AS hobby
FROM my_table,
LATERAL FLATTEN(input => json_data:hobbies) AS hobby;

SELECT
    OBJECT_KEYS(json_data) AS keys
FROM my_table;

SELECT
    ARRAY_SIZE(json_data:hobbies) AS num_hobbies
FROM my_table;