pgsql - PostgreSQL 9.2-Convertir la cadena json TEXT al tipo json/hstore

postgresql jsonb example

Question

Tengo una columna TEXT que contiene una cadena JSON válida.

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"}'');

Quiero transformar algunos campos en un formato de consulta.

Se REGEXP_REPLACE un REGEXP_REPLACE para cada campo (campo de language y campo de gender ). Pero como es JSON válido, hay una manera de:

• Convertir en tipo JSON
• Convertir en tipo hstore
• O cualquier otra forma factible.

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

Answer 1

Aquí hay una solución de Postgresql: Convertir columnas TEXT a JSON :

ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING col1::JSON;

Answer 2

O de una manera más corta que Reza:

SELECT settings::json FROM users;

Luego, para seleccionar el idioma por ejemplo:

SELECT settings::json->>''language'' FROM users;

Más detalles sobre la documentación oficial .

Answer 3

Si necesita un índice en él, cree una función inmutable que tome el json como entrada y produzca el campo que desea como salida en un lenguaje pl, por ejemplo:

create function extract_language(text) returns text as $$
  -- parse $1 as json
  -- return $1.language
$$ language whatever immutable;

Luego agregue un índice en la expresión:

create index users_language on users(extract_language(settings));

El índice se utilizará (potencialmente) en consultas como:

select * from users where extract_language(settings) = ''en'';

Answer 4

SELECT cast(settings AS json) from users;