Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
Showing results for 
Search instead for 
Did you mean: 

VIEW JSON result value in view which based on volume

Contributor III

Hello guys!

I have the following case:

It has been decided that the json file will be read from a following definition ( from volume) , which more or less looks like this:


CREATE OR REPLACE VIEW  [catalog_name].[schema_name].v_[object_name]
SELECT result, _metadata.file_path  as etl_full_path, _metadata.file_modification_time as etl_inserted_at  FROM json.`dbfs:/Volumes/[catalog_name]/[schema_name]/[volume_name]/object=[object_name]/`


I would like to read this view using SQL warehouse and I can not view result column value - there is a  NULL value 

despite the flag is turn on  in view properties.




Any tips? 



Community Manager
Community Manager

Hi @CamiIt seems you’re encountering an issue with the NULL value in the result column of your view despite having the flag enabled.

Let’s address this step by step:

  1. View Column Not Null Issue:

    • When creating a view, SQL Server sometimes believes that a column can be NULL, even when you expect it to be NOT NULL.

    • The trick here is to use the ISNULL function on the outside of your expression to ensure that the resulting value can never be NULL.

    • In your case, you want to create a view with a BIT column named HasStatus based on the Status column from the Product table.

    • Here’s how you can modify your query to achieve this:

      CREATE OR REPLACE VIEW [catalog_name].[schema_name].v_[object_name]
                  WHEN Status = 3 THEN 1
                  ELSE 0
              END AS bit), 0) AS HasStatus
      FROM dbo.Product;
    • This ensures that the HasStatus column will be NOT NULL and of type BIT.

  2. Flag for Empty Strings in JSON:

    • You’ve set the flag view.sqlConfig.spark.sql.legacy.json.allowEmptyString.enabled=true to handle empty strings in your JSON data.
    • Make sure that this flag is correctly configured in your environment.
    • Additionally, ensure that the result column in your JSON data doesn’t contain empty strings, as they might be interpreted as NULL values.
  3. Testing the View:

    • After creating the view, you can query it to verify that the HasStatus column behaves as expected.
    • If you encounter any issues, double-check your configuration and the actual data in the result column.

Remember that the ISNULL function is your ally when dealing with computed columns in views. It ensures that the result is never nullable, which can be especially useful when working with ORMs or other applications that expect non-nullable values. 🛠👍

Feel free to test the modified view and let me know if you need further assistance! 😊


Thank you for response.

But there no issue with NULL value when I query view created on my cluster which has set the flag

spark.sql.legacy.json.allowEmptyString.enabled = True

Without that flag on session or cluster I get NULL value querying json.
The issue with a NULL value occurs when I query view via SQL warehouse compute.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!