get_json_object and json path filtering

MadCowTM — Thu, 06 Jun 2024 10:04:08 GMT

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 🙂

Re: get_json_object and json path filtering

brickster_2018 — Mon, 10 Jun 2024 08:59:44 GMT

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'

topic get_json_object and json path filtering in Get Started Discussions

get_json_object and json path filtering

Re: get_json_object and json path filtering