- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-06-2024 01:06 AM - edited 06-06-2024 03:04 AM
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 🙂
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-10-2024 01:59 AM
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'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-10-2024 01:59 AM
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'

