Databricks explicitly insert null into struct column

Question

I'm facing an issue when inserting records into struct field in Azure Databricks using SQL.

In my INSERT INTO statement I have a condition based on which I want to insert either null or a struct.

INSERT INTO my_table (target_column_name)
SELECT
CASE 
WHEN condition = true THEN NAMED_STRUCT(<some_fields>) 
ELSE NULL 
END AS target_column_name
FROM source_table

When I run SELECT only, I'm getting struct when condition is met and null when not met.

However, when I run it as INSERT INTO and check the target table, I'm getting a struct with all fields set to null instead of a single null value.

When I run two separate INSERT statements (one for met condition and second for not met), I'm getting single null value.

I want to avoid having two separate insert statements, how can I force the null value?

Try this: ELSE CAST(NULL AS STRUCT<field1: TYPE1, field2: TYPE2>) This should insert a true NULL instead of a null-filled struct — Mihir Saxena
– Mihir Saxena, Commented Jun 6 at 16:19

Mihir Saxena · Accepted Answer · 2025-06-09 09:36:30Z

1

Your SELECT query behaves correctly: it returns either a valid Struct or NULL. But when used inside an INSERT INTO, the NULL becomes a STRUCT with all fields set to null (not a true NULL).

This is happening because Spark infers the result type of the CASE expression as a STRUCT. When NULL is used without a cast, it’s interpreted as a struct with null fields, not an actual NULL.

To force the insertion of an actual NULL, explicitly cast the NULL to the struct type like this:

INSERT INTO my_table (target_column_name)
SELECT
  CASE 
    WHEN condition = true THEN NAMED_STRUCT('field1', value1, 'field2', value2)
    ELSE CAST(NULL AS STRUCT<field1: TYPE1, field2: TYPE2>)
  END AS target_column_name
FROM source_table;

Casting NULL explicitly tells Spark/Databricks that you want a true SQL NULL of type STRUCT, not a STRUCT object with all fields null.

So, I tested this whole scenario and here's the result after inserting the data using the explicit cast method:

Creating source and target table:

Running the INSERT query:

Verifying the result with this select:

is_struct_null = true confirms a proper null was inserted instead of a struct with null fields.

answered Jun 9 at 9:36

Mihir Saxena

4253 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cornisto Jun 13 at 8:07

Thank you! I tried that before but it didn't work properly because of a bug in the function that was being called to generate that struct for condition = true. When I fixed the function your approach worked

Collectives™ on Stack Overflow

Databricks explicitly insert null into struct column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related