Json string to column with colon:
SELECT customer_id, profile:first_name, profile:address:country
FROM customers
Json string to struct type with from_json
- struct type → allow interaction with nested object in the preview/ allow
- schema of the json required, which can be obtained by schema_of_json() with a sample data
--create a struct type
SELECT profile
FROM customers
LIMIT 1
CREATE OR REPLACE TEMP VIEW parsed_customers AS
SELECT customer_id, from_json(profile, schema_of_json('{"first_name":"Thomas","last_name":"Lane","gender":"Male","address":{"street":"06 Boulevard Victor Hugo","city":"Paris","country":"France"}}')) AS profile_struct
FROM customers;
SELECT * FROM parsed_customers
- the subfield of a struct type can be obtained with a dot:
SELECT customer_id, profile_struct.first_name, profile_struct.address.country
FROM parsed_customers
- struct type can be flatten with a *:
CREATE OR REPLACE TEMP VIEW customers_final AS
SELECT customer_id, profile_struct.*
FROM parsed_customers;
SELECT * FROM customers_final
explode array struct type into multiple rows with explode():
SELECT order_id, customer_id, explode(books) AS book
FROM orders
- use collect_set() aggregated function to collect the distinct item in a array for each group
- use flatten() to flatten nested array into a one-dirmension array
- use array_distinct() to only retain the distinct value in an array
Join & Set operation
- inner, outer, left, right, anti, cross, semi joins
- union, intersect, EXCEPT
- Pivot, unpivot
Streaming-static join
Although Streaming-static table can be joined together, full outer join is not available, as the system cannot guarantee if we will get matching records