New SQL editor autocomplete doesn't work on nested JSON

[yit]

I have nested JSON objects stored as values in some table columns. The table has a few top-level columns, and inside those, there are further nested JSON structures.

When I write queries in the new SQL editor, the autocomplete correctly lists the first- and second-level fields. However, for deeper nested fields, it starts suggesting the first- and second-level fields again instead of the actual deeper fields.

Since Databricks infers the schema from the data, is it possible to configure autocomplete to suggest only the correct (available) nested fields at each level?

[BigRoux · Accepted solution]

Hope this helps:

Based on documentation, Databricks' new SQL editor supports autocomplete for top-level and certain second-level fields of nested JSON objects, but it does not currently provide accurate, context-aware autocomplete for deeper nested fields. 

Cheers, Louis

[nayan_wylde]

@yit I could think of these possible reasons.

1. Autocomplete relies on metadata to provide suggestions. Ensure the workspace is enabled for Unity Catalog and that the user has the necessary permissions to access the metadata for the tables containing the nested JSON data.

2. Autocomplete might not yet fully support the intricacies of nested JSON path expressions and schema inference. This might be an area of ongoing development or a known issue being addressed by Databricks. You can email databricks support for it.

3. Consider defining the schema for your JSON data, especially for nested structures, as it may help with autocomplete accuracy instead of using infer schema.