I have a database table which has a column having data stored in a format similar to store json array. To help make things clear, let's say the table is called browser having two columns -> id and json_data.
| ID| _________________________________________ json_date __________________________________________________________________________________________________________. |
| --|----------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 | [{"key"=>"userAgent", "value"=>"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36"}, {"key"=>"webdriver", "value"=>"not available"}, {"key"=>"language", "value"=>"en"}, {"key"=>"colorDepth", "value"=>24}, {"key"=>"deviceMemory", "value"=>8}, {"key"=>"hardwareConcurrency1234d", "value"=>4}] |
|---|------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| 2 | [{"key"=>"userAgent", "value"=>"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.98 Safari/537.39"}, {"key"=>"webdriver", "value"=>"not available"}, {"key"=>"language", "value"=>"en"}, {"key"=>"colorDepth", "value"=>24}, {"key"=>"deviceMemory", "value"=>16}, {"key"=>"hardwareConcurrency1234d", "value"=>4}] |
|---|------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
In this data, I want to extract the json/hstore value where the key is 'deviceMemory'. I am using PostgreSQL in Metabase. I've tried to use json_array_elements and hstore_to_json functions but haven't been able to resolve this. Could someone help me out in this?