cancel
Showing results for 
Search instead for 
Did you mean: 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results for 
Search instead for 
Did you mean: 

get_json_object and json path filtering

MadCowTM
New Contributor II

I have following string

 

[{"key":"abc","value":{"string_value":"abc123"}},{"key":"def","value":{"int_value":123}},{"key":"ghi","value":{"string_value":"ghi456"}}]

 

and from that string i need to extract key.value.string_value for key with the value equal to "ghi"

i ran test in the jsonpath(dot)curiousconcept(dot)com and got working path condition to get to such value

 

$[?(@.key == 'ghi')].value.string_value

 


result is: "ghi456" as expected

however when i run this in dbx, i got always null and i am not sure if path filtering works there at all

 

select get_json_object('[{"key":"abc","value":{"string_value":"abc123"}},{"key":"def","value":{"int_value":123}},{"key":"ghi","value":{"string_value":"ghi456"}}]', '$[?(@.key == "ghi")].value.string_value')

 

Above query always returns null.

Is it DBx implementation of get_json_object and json path within it being not exactly regular json path or do i have to something else to get to the value as in the example?

Thank you a lot for your time and help 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

brickster_2018
Databricks Employee
Databricks Employee

Can you try with the below code snippet

WITH exploded_json AS (
  SELECT explode(from_json(
    '[{"key":"abc","value":{"string_value":"abc123"}},{"key":"def","value":{"int_value":123}},{"key":"ghi","value":{"string_value":"ghi456"}}]', 
    'array<struct<key:string,value:struct<int_value:int,string_value:string>>>'
  )) as json_element
)
SELECT json_element.value.string_value
FROM exploded_json
WHERE json_element.key = 'ghi'

View solution in original post

1 REPLY 1

brickster_2018
Databricks Employee
Databricks Employee

Can you try with the below code snippet

WITH exploded_json AS (
  SELECT explode(from_json(
    '[{"key":"abc","value":{"string_value":"abc123"}},{"key":"def","value":{"int_value":123}},{"key":"ghi","value":{"string_value":"ghi456"}}]', 
    'array<struct<key:string,value:struct<int_value:int,string_value:string>>>'
  )) as json_element
)
SELECT json_element.value.string_value
FROM exploded_json
WHERE json_element.key = 'ghi'

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now