get_json_object and json path filtering

MadCowTM · ‎06-06-2024

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 🙂

brickster_2018 · ‎06-10-2024

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