PostgreSQL 9.2 - Convert TEXT json string to type json/hstore

Question

I have a TEXT column containing valid JSON string.

CREATE TABLE users(settings TEXT);  INSERT INTO users VALUES ('{"language":"en","gender":"male"}'); INSERT INTO users VALUES ('{"language":"fr","gender":"female"}'); INSERT INTO users VALUES ('{"language":"es","gender":"female"}'); INSERT INTO users VALUES ('{"language":"en","gender":"male"}'); 

I want to transform some fields into a query-able format.

A REGEXP_REPLACE for each field would do (language field and gender field). But since it's valid JSON, is there way to:

• Convert into JSON type
• Convert into hstore type
• Or any other feasible ways

SQLFiddle: http://sqlfiddle.com/#!12/54823

Answer 1

SELECT cast(settings AS json) from users; 

EDIT 7 years later

I highly suggest that you don't use unstructured columns unless your data is unstructured. RDBMS go a very long way. We built a fairly large platform and used user settings as a json column, and it endedup becoming a junk drawer which needed to be cleaned up many years later