PostgreSQL 9.2 - Преобразование TEXT json string для ввода json/hstore

У меня есть столбец TEXT, содержащий допустимую строку JSON.

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

Я хочу преобразовать некоторые поля в формат, пригодный для запросов.

A REGEXP_REPLACE для каждого поля будет делать поле (language и поле gender). Но поскольку он действительно JSON, существует способ:

Преобразование в тип JSON
Преобразование в тип hstore
Или любые другие возможные способы

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

Ответы

Ответ 1

SELECT cast(settings AS json) from users;

Ответ 2

Или самым коротким способом, чем Реза:

SELECT settings::json FROM users;

Затем для выбора языка, например:

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

Подробнее о официальной документации .

Ответ 3

Вот решение от Postgresql: преобразование столбцов TEXT в JSON:

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

Ответ 4

Если вам нужен индекс, создайте неизменяемую функцию, которая принимает json в качестве входных данных и выдает поле, которое вы хотите, в качестве вывода на языке pl, например:

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

Затем добавьте индекс в выражение:

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

Затем индекс будет (потенциально) использоваться в запросах, таких как:

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

Ответ 5

Таким образом, у меня была проблема, где текст был JSON. Если у вас есть эта проблема, используйте этот запрос. Где COLUMN - это столбец, который содержит тип данных JSONB или JSON, а ATTRIBUTE - это атрибут JSON, представляющий собой строку, которую вы хотите преобразовать в JSON.

Текст будет выглядеть так: "{\" junk5\": 283774663, \" junk2\": 0, \" junk1\": 1218478497, \" junk3\": 1923, \" junk4\": 63278342}"

SELECT CAST(TRIM(both '"' from jsonstring) as JSON)
FROM (
    SELECT REPLACE(cast(COLUMN->'ATTRIBUTE' as text), '\"', '"')
    as jsonString from TABLE where cast(COLUMN->'ATTRIBUTE' as text)LIKE '%\\%'
) as JSON_CONVERTING