To create a table directly from a JSON file and flatten it using SQL in Databricks, you can use the CREATE TABLE statement with the USING JSON clause. However, SQL alone does not provide a direct way to flatten nested JSON structures. You would typically need to use a combination of SQL and DataFrame operations to achieve this.

Here is an example of how you can create a table from a JSON file using SQL:

-- Create a table from a JSON file
CREATE TABLE your_table_name
USING JSON
OPTIONS (path 'path/to/your/json/file');

• Replace 'path/to/your/json/file' with the actual path to your JSON file.

To flatten the nested JSON structure, you would need to use DataFrame operations in combination with SQL. Here is an example of how you can achieve this using PySpark:

# Read the JSON file into a DataFrame
df = spark.read.json("path/to/your/json/file")

# Flatten the nested JSON structure
from pyspark.sql.functions import explode
df_flattened = df.withColumn("Price", explode(df.Prices)).select("Clnt", "NaDt", "PrCd", "IFCd", "GLId", "Price.*")

# Create a temporary view from the DataFrame
df_flattened.createOrReplaceTempView("temp_view")

# Use SQL to create a table from the view
spark.sql("""
    CREATE TABLE your_table_name AS
    SELECT * FROM temp_view
""")