PostgreSQL 9.2 - 将 TEXT json 字符串转换为 json/hstore 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16074375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL 9.2 - Convert TEXT json string to type json/hstore
提问by huy
I have a TEXTcolumn containing valid JSON string.
我有一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"}');
I want to transform some fields into a query-able format.
我想将某些字段转换为可查询的格式。
A REGEXP_REPLACEfor each field would do (languagefield and genderfield). But since it's valid JSON, is there way to:
REGEXP_REPLACE每个字段的A都可以（language字段和gender字段）。但由于它是有效的 JSON，有没有办法：
- Convert into JSON type
- Convert into hstore type
- Or any other feasible ways
- 转换成 JSON 类型
- 转换成hstore类型
- 或任何其他可行的方法
SQLFiddle: http://sqlfiddle.com/#!12/54823
SQLFiddle：http://sqlfiddle.com/#!12/54823
回答by Reza S
SELECT cast(settings AS json) from users;
回答by Jonathan Petitcolas
Or in a shortest way than Reza:
或者以比 Reza 最短的方式：
SELECT settings::json FROM users;
Then, for selecting language for instance:
然后，例如选择语言：
SELECT settings::json->>'language' FROM users;
More details on the official documentation.
更多细节请参见官方文档。
回答by Hua Zhang
Here is a solution from Postgresql: Converting TEXT columns to JSON:
这是来自Postgresql的解决方案：将 TEXT 列转换为 JSON：
ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING col1::JSON;
回答by Hymanstine
So I had an issue where the text was JSON. If you have this issue use this query instead. Where COLUMN is the column that contains the JSONB or JSON datatype and ATTRIBUTE is the attribute of the JSON that is a string, that you want converted into JSON.
所以我遇到了文本是 JSON 的问题。如果您遇到此问题，请改用此查询。其中 COLUMN 是包含 JSONB 或 JSON 数据类型的列，ATTRIBUTE 是字符串 JSON 的属性，您希望将其转换为 JSON。
The text will look like this, "{\"junk5\": 283774663, \"junk2\": 0, \"junk1\": 1218478497, \"junk3\":1923, \"junk4\": 63278342}"
文本看起来像这样，"{\"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
回答by Denis de Bernardy
If you need an index on it, create an immutable function that takes the json as input and yields the field you want as output in a pl language, e.g.:
如果您需要一个索引，请创建一个不可变的函数，该函数将 json 作为输入并以 pl 语言生成您想要的字段作为输出，例如：
create function extract_language(text) returns text as $$
-- parse as json
-- return .language
$$ language whatever immutable;
Then add an index on the expression:
然后在表达式上添加一个索引：
create index users_language on users(extract_language(settings));
The index will then (potentially) get used in queries such as:
然后索引将（可能）用于查询，例如：
select * from users where extract_language(settings) = 'en';
