PostgreSQL 9.2 - 将 TEXT 类型的 json 字符串转换为 json/hstore 类型

Question

我有一个包含有效JSON字符串的TEXT列。

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

我希望将一些字段转换为可查询的格式。

对于每个字段，使用REGEXP_REPLACE即可（language字段和gender字段）。但是由于它是有效的JSON格式，是否有办法：

• 转换为JSON类型
• 转换为hstore类型
• 或者其他可行的方法

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

- huy

---

请求的功能是9.3版本中有趣功能之一。我读过一些文章，描述了使用pl/js8实现此目的的方法http://www.postgresonline.com/journal/archives/263-PLV8JS-and-PLCoffee-Part-2-JSON-search-requests.html。 - Pavel Stehule

谢谢。我也发现可以通过将其转换为JSON来完成第一个，即settings::json。但是它不能进行查询。最终使用了正则表达式。 - huy

FYI，针对 PostgreSQL 9.2，有一个9.3 JSON函数增强的后移版本。http://adpgtech.blogspot.co.nz/2013/04/backport-of-93-json-enhancements.html - Mike T

Answer 1

或者比Reza更简短的方式：

SELECT settings::json FROM users;

那么，例如选择语言：

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

更多详细信息，请参阅官方文档。

- Jonathan Petitcolas

---

我知道这已经很旧了，但是：如何在文本中搜索特定字段，比如WHERE settings :: json ='team_id'：team_id（也就是说，在设置的文本中搜索特定字段）？ - Pille

@Pille settings::json ->> 'team_id' = team_id（注：此为程序相关内容，非自然语言） - PRMan

Answer 2

SELECT cast(settings AS json) from users;

7年后的编辑

我强烈建议除非你的数据是非结构化的，否则不要使用非结构化列。关系型数据库可以帮你很好地解决问题。我们曾经构建了一个相当大的平台，将用户设置存储在一个json列中，结果它变成了一个需要多年后才能清理的杂物抽屉。

- Reza S

---

我也需要完全相同的东西。这个工具很好地完成了任务。话虽如此，由于我正在查询一个相对较小的数据集，所以我无法确定它的性能如何。 - slant

我希望这在文档的最开始就被说明了。或者如果我已经忽略了，我希望它更加显眼。将数据库中的varchar转换为json花费了太长时间。感谢您的帖子。 - Dirk Schumacher

我记得这个以前是可以工作的，但最近我收到了“无效输入语法”的返回，似乎找不到有用的文档来说明如何将“有效”的字符串化 JSON 写入 varchar 字段。 - GPP

Answer 3

::jsonb 不适用于你的列已经是 json 类型且根级别包含字符串的情况。以下是一行代码将这样的字符串转换为 JSON:

SELECT (settings #>> '{}')::jsonb -> 'language' from users;

我在这里找到了这个答案。

该语句首先通过空路径给出的#>>运算符提取根级字符串作为文本。请注意，仅仅将这样的字符串强制转换为文本 (::text) 是无法工作的，因为它会转义所有引号。 接下来，这样提取的字符串被解析为json对象 (::jsonb)。

这个查询的另一个版本是将json字符串放入数组中，然后提取第一个元素作为文本：

选择转换为json的所有根级别带有字符串字段的一种解决方法是使用以下命令：

UPDATE users
SET
    settings = settings #>>'{}'::jsonb
WHERE settings ->> 'language' is  NULL

- Piotr Czapla

---

太棒了，这正是我所需要的。我非常自豪并感到谦卑，因为我成为你在这个回答上第一个点赞的人。 :-) 先生，请继续保持你出色的工作。 - Chris W.

谢谢Chris的赞美之词！你让我今天过得很愉快 :)。 - Piotr Czapla

同意！！！你通过这篇文章帮我省了不少钱！！谢谢！ - TheChrisONeil

我使用转义保存了一些文本，这种方式也起作用了。谢谢，先生！您解决了困难。 - Thiago Cavalcanti

Answer 4

这里有一篇来自PostgreSQL:将TEXT列转换为JSON的解决方案：

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

- Hua Zhang

---

这需要重写表格吗？ - davidtgq

不确定这是否解决了所提出的问题，更改类型将需要重构表。 - Illegal Operator

该死！这个完美地运作了！ - undefined

Answer 5

我曾经遇到一个问题，即文本为JSON格式。如果您遇到此问题，请使用以下查询。其中COLUMN是包含JSONB或JSON数据类型的列，ATTRIBUTE是您想要转换为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

- Jackstine

---

这是我的一行代码：update users set settings = regexp_replace(trim(both '"' from settings), '\\"', '"', 'g')::json; - davetapley

如果要转换的json中包含另一个json作为字符串，则此方法将失败。 更安全的方法是将字符串提取为文本，然后将其转换为json： `select settings #>>'{}'::jsonb from users;. 请参见下面的答案以获取更长的描述。 - Piotr Czapla

Answer 6

补充一下其他评论，这里是一个不需要更新查询即可完成的一行代码。

regexp_replace(trim(both '"' from settings::text), '\\"', '"', 'g')::json as column_name;

- Rodrigo Richter

Answer 7

如果您需要对其创建索引，请使用不可变函数在pl语言中创建一个以json为输入并产生所需字段作为输出的函数，例如：

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';

- Denis de Bernardy