Q:

Join an id to a look-up table that is stored as a text string in Postgres

In my customer table, customer_state_value is just a number between 1 and 50. I am trying to join the customer_state_value from my customer table to the state lookup table to get the state name. 
However, my state lookup 'table' is really just a 1*1 text field that stores data like below:
[{"id : "1", "label": "Alabama"}, {"id : "2", "label": "Alaska"}...]
What is the best way to get the state value through sql join? 

A:

Your database schema is a bit weird and if you can, I would suggest you to change your lookup table to a "normal" format.
But if you can't, you should be able to achieve a normal join using json function in postgres. You can refer to this page:Postgres/json. I didn't test but something like:
select * from json_each(your_field::json->0)

But i found this solution a bit overkill.

