Parse json stored in `text` column

I’ve seen many questions about parsing json on Stack Overflow, but none quite get me where I need to be.

I have a table such as

 _____________________________________
|                                     |
| PreferenceId::varchar | Value::text |
 _____________________________________
|                                                                                  |
| 1   | [{"username":"test","customerId":"504116aa-bf95-4736-8322-917e5055681d"}]  |

I ‘ve tried jsonb_array_elements and json_to_recordset but I continually get errors in postgres when I attempt to parse and query this data. usually errors such as function jsonb_array_elements doesn't exist etc.

When I try to use a function with a static test query it works fine.

select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);

I’ve tried casting the column to json and using object notation, but it doesn’t work either

select v."Value"::json->>'username' --- username comes back as null

>Solution :

Here is how to do it using json_array_elements, note that this function accepts JSON as parameter not text :

select j->>'username', j->>'customerId'
from mytable, json_array_elements(Value::json) as j