Issue while extracting value From Decimal Key is Json

[data_guy]

Hi Guys,

I have a JSON as the below structure where the key is as decimal.
{ "5.0": { "a": "15.92", "b": 0.0, "c": "15.92", "d": "637.14" }, "0.0": { "a": "15.92", "b": 0.0, "c": "15.92", "d": "637.14" } }
schema_of_json returns the following:

STRUCT<`0.0`: STRUCT<a: STRING, b: DOUBLE, c: STRING, d: STRING>, `5.0`: STRUCT<a: STRING, b: DOUBLE, c: STRING, d: STRING>>

I am facing an issue in extracting values against (a,b,c,d) for 5.0 and 0.0 in Databricks SQL. Following are the methods that I have tried, but still not able to extract value as it is coming NULL.

SELECT 
data,
data:`5.0` as method1,
get_json_object(data, '$.`5.0`') as method2
FROM staging.consumables.test;

Can you guys please help?

[raphaelblg · Accepted solution]

Hello @data_guy ,

I've performed a reproduction of your scenario and could successfully select all data. Please check screenshot below: 

This is the source code:

import json

data_values = { 
               "5.0": { "a": "15.92", "b": 0.0, "c": "15.92", "d": "637.14" }, 
               "0.0": { "a": "15.92", "b": 0.0, "c": "15.92", "d": "637.14" } 
}

json_str = json.dumps(data_values)
dbutils.fs.put("dbfs:/tmp/data_temp.txt", json_str, True)

df = spark.read.json("dbfs:/tmp/data_temp.txt")
df.createOrReplaceTempView("JSON_TEST")

spark.sql("""
SELECT 
    `5.0`.a,
    `5.0`.b,
    `5.0`.c,
    `0.0`.a,
    `0.0`.b,
    `0.0`.c
FROM JSON_TEST
"""
).display()