Extract a JSON property with an asterisk in the property name using get_json_object() in SparkSql

user_787960 0
Viewed 399

I have a table in Databricks that has a column (called "properties") which contains JSON data. I've successfully used get_json_object() in a SparkSql notebook to retrieve properties from it like so:

%sql
select distinct_id, get_json_object(properties, "$.time")
from my_table

This works well. However, there are sub-properties in the properties column that have asterisks in their names, e.g. *Plan. Accessing these properties in the standard way, e.g. $.*Plan doesn't work, since * has special meaning for get_json_object(). I've tried accessing these properties using escape chars, like so:

%sql
select distinct_id, get_json_object(properties, "$.\*Plan")
from my_table

... along with alternative escapes, but to no avail. Is there a way to extract JSON sub-properties that can escape the asterisk?

Thanks!

databricks apache-spark-sql apache-spark
1 Answers

You can use LATERAL VIEW and json_tuple as a workaround. It's not so fussy about special characters eg:

SELECT x.*
FROM my_table
  LATERAL VIEW json_tuple( properties, '*Plan' ) x

Or if you are wedded to using get_json_object you can clean up the string beforehand (although you have somewhat defeated the point of using JSON):

%sql
select 
  distinct_id, 
  get_json_object(replace(properties, '*', ''), '$.Plan' ) z
from my_table

I couldn't personally get any escaping methods to work ( eg \u0042 or \ ) but happy to be corrected.

Ideally, don't put such strange characters in your JSON in the first place.

user_15275040
Related