Querying Semi-structured Data using Snowflake

Typically, hierarchical data has been imported into a VARIANT from one of the following supported data formats:

• JSON

• Avro

• ORC

• Parquet

(For information about querying XML data (e.g. data that originated in XML data format and was converted to an OBJECT by calling PARSE_XML), see XMLGET.)

In this Topic:

• Sample Data Used in Examples

• Traversing Semi-structured Data
  

  • Dot Notation

  • Bracket Notation

Using DOT notation

Get the names of all salespeople who sold cars:

select src:salesperson.namefrom car_salesorder by 1;

Using Bracket notation select src['salesperson']['name']from car_salesorder by 1;

Source : https://docs.snowflake.com/en/user-guide/querying-semistructured.html