Spark SQL | Any depth path ($..foo) not working with get_json_object

user_1727311 0
Viewed 315

The following code sample should ideally return the value of property value but instead returns null. Is this expected? From the spark documentation, it seems any depth query is supported by spark sql and the spark test suite has an example, but unfortunately its for a negative test case.

spark.sql("""select get_json_object('{"node":{"value":"abc"}}', '$..value') as j""").show()

Expected output - abc / Actual - null

I'm trying the any depth path because the json column in every row in the dataset has a variable schema and we're searching for a given key, say foobar, anywhere in the given json, so in the above example, $.value or $.foo.value for more nested values isn't something that's feasible.

apache-spark-sql apache-spark json
1 Answers

The function get_json_object does not support recursive paths as the test show it, so you can't use it in this particular case. If you know the schema of you json string then I'd recommend you to use from_json function to convert it into a struct type which would be easier to handle.

However, you can always try using regexp_extract with some regex like:

"key"\s?:\s?"([\w\s]+)"

Example:

spark.sql("""
  SELECT regexp_extract('{"node":{"value":"abc"}}', '"value"\\s?:\\s?"([\\w\\s]+)"', 1) AS value
""").show()

//+-----+
//|value|
//+-----+
//|  abc|
//+-----+
user_13865510
Related