Loading Databricks updates...
← All video guides
Databricks SQL & AI/BI

VARIANT Data Type: Query Semi-Structured JSON in Databricks

5 min readNextGenLakehouse

Learn how to query semi-structured JSON data with Databricks VARIANT data type—no schema definition required.

In short

Learn how to query semi-structured JSON data with Databricks VARIANT data type—no schema definition required.

Source: official Databricks documentation · Databricks announcement

Key takeaways

  • VARIANT data type enables flexible schema-on-read querying of JSON without enforcing strict schemas.
  • Six VARIANT functions: parse_json, to_json, variant_get, cast, variant_schema, variant_explode support comprehensive JSON operations.
  • COPY INTO with VARIANT ingests JSON files directly; nested field access uses dot notation and bracket indexing.
  • VARIANT uses offset-based binary format for performance; schema-less ingestion supports evolving JSON structures.

Chapters

  • 0:00 — Introduction: JSON handling challenges and VARIANT overview
  • 0:36 — Why VARIANT: flexibility without schema enforcement
  • 1:07 — Six VARIANT functions explained
  • 2:02 — Demo: Parsing JSON and field access with parse_json()
  • 3:12 — Demo: Ingesting JSON files with COPY INTO and VARIANT
  • 4:14 — Advanced: Nested field access and array iteration

Frequently asked questions

When should I use VARIANT versus schema inference?

Use VARIANT for unknown, changing, or frequently evolving schemas. Use schema inference for static, well-defined structures. VARIANT is flexible and performant; schema inference is strict.

How do I parse JSON with VARIANT?

Use parse_json() to convert JSON strings to VARIANT, then access nested fields with dot notation (data:age) or bracket indexing for arrays.

Can I ingest JSON files directly into VARIANT columns?

Yes, use COPY INTO with a VARIANT-type column and format_options = ('document = single'); entire JSON object maps to single VARIANT column.

How do I extract values from arrays in VARIANT?

Use variant_get() with array index (data:friends[0]:name) or lateral variant_explode() to unnest arrays into rows.

Full transcript

Auto-generated from the video and lightly edited for readability. Timestamps link to the moment in the video.

Show full transcript (14 segments)

0:05hey today we are going to talk about the most efficient way to handle semistructured data in the lake housee as you know Json is the most popular format to handle semi-structured data because of its flexibility and also because it's supported by most programming languages and Chason can be handled in with in two options in data

0:27breaks the first option is you cre data as a string this option is very easy and flexible but paring the data is very slow and the second option is schema inference you must determine a schema that works with all the data and keep in mind that schema enforcement is very strict which means that incoming data must be compatible with the existing

0:50schema so it's either too strict or too slow but thanks to variant things are going to change first of all because variant data type is open it has been implemented for Apache spark and Delta Lake it's flexible because it takes schema on rate approach and performance thanks to the offset based binary

1:12format variant is very useful when the Jon sources have unknown changing and frequently evolving schema and let me show you in this demo how to get started with varing data type quickly the first thing I'm going to use my catalog called Delta demo and as as you can see we have six new functions the first one is bar Chason and this one is used to convert

1:36Chason Jon to variant the second one to Chason of course to go from variant to cast to convert variant to Jon string variant G is to extract the B of specified type from variant cast is two and from variant schema variant so this one's going to return the schema uh string of variant and last last but not

2:00least variant explode which is a table function for NS in a variant so let's have a look at the first example I'm just creating a table and I'm using the function par Json and inside this function I'm going to put a Json file so I'm going to create uh this table called uh store data and then I'm

2:22going to use uh or I'm going to access this data because my table is called row so going to row and I'm going to access for example the owner so basically over here I'm going to have the result the owner is Emy or I can uh access if I remember the schema so it's need to be

2:44store store and bicycle and then of course I can access color right so this is a very easy way to access data if I want to use the variant explod to get all this data in

3:06one row so as you can see over here I have the key basket bicycle book fruit and the value this is the result of my Chase on file and the second option is to create uh a table with a column called with the type variant and then I'm going to use the

3:28copy into uh function to copy Chason data that I already put in my volume I specify the format which is Json and then I'm adding a format option to specify a single variant column and of course this column should be the column the variant column type so now I can just run the select

3:53star so this is how the data looks like now I'm going to start or try to understand the schema of my variant type so I have the object age is begin CT string friends is an object inside I have hobbies and the name of the name of the friend then I can access the

4:17data so data is my variant data type 2. h access the age data C and of course I can access information I have inside my uh array so this how it looks like a78 the C is Austin friends hobbies and friends name Hobby and so on I can also use the variant get so over here I'm accessing

4:40the friends the first friend and I'm getting the Hobbies so see over here see watching sport reading skiing snowboarding and again I can also use the variant explode over here so I'm just going to get all the different Hobbies I'm using the variant get over here I'm using a and then I'm using a

5:04lateral uh variant explode so I'm combining between variant get and variant explode and over here I get all the list of uh hobbies and this is that was a quick example to uh walk you through the variant uh data type thank you

Official Databricks resources

Related

Databricks Answers

Looking for a quick, source-backed answer? Browse Databricks Answers on Databricks SQL, dashboards & analytics — direct, documentation-validated answers to common Databricks questions, curated by Youssef Mrini, Senior Developer Advocate at Databricks.