Issue while extracting value From Decimal Key is Json

data_guy — Fri, 11 Aug 2023 14:23:02 GMT

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?

Re: Issue while extracting value From Decimal Key is Json

raphaelblg — Mon, 27 May 2024 21:59:38 GMT

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()

topic Re: Issue while extracting value From Decimal Key is Json in Warehousing & Analytics

Issue while extracting value From Decimal Key is Json

Re: Issue while extracting value From Decimal Key is Json