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'