Menu
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:
SQLFiddle: http://sqlfiddle.com/#!12/54823
620
You can save any valid json value to either json or to a jsonb column. But you cannot bind it as string/ text / varchar , if you use prepared statements (use casts instead in sql, like UPDATE ... SET json_col = $1::json or bind it as unknown ).
PostgreSQL offers two types for storing JSON data: json and jsonb . To implement efficient query mechanisms for these data types, PostgreSQL also provides the jsonpath data type described in Section 8.14. 7. The json and jsonb data types accept almost identical sets of values as input.
Querying JSON dataPostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field by key. The operator ->> returns JSON object field by text.
Jsonb stores the data as binary code. Basically, it stores the data in binary form which is not an ASCII/ UTF-8 string.
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
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
