使用Postgres解析字符串化的JSON对象

Question

我一直在使用Postgres将JSON对象存储为字符串，现在我想利用PG内置的json和jsonb类型来更有效地存储对象。

基本上，我想要在PG内解析字符串化的JSON并将其放入json列中，而不必借助Python读取所有值并在那里进行解析。

理想情况下，我的迁移应该如下所示：

UPDATE table_name SET json_column=parse_json(string_column);

我查看了Postgres的JSON函数，似乎没有一种方法可以做到这一点，即使这似乎很简单。记录一下，我的JSON对象只是由字符串组成的一维数组。

有没有办法实现这个？

- Yotam Ofek

---

json 和 jsonb 并不会更有效地 存储 对象。如果您只是存储和检索 JSON 值，则最好坚持使用 text。如果您希望数据库对数据进行处理（jsonb）或检查其完整性（json），则 JSON 类型很有趣。 - Laurenz Albe

实际上，jsonb确实更有效地存储对象，因为它将其存储为二进制表示，而不是字符串表示。对于大型数据集，这可以在存储空间方面带来关键的收益，这也会影响索引大小等方面。其次，将JSON存储为文本与将整数存储为文本一样愚蠢。即使没有实际需要，数据清晰度也应该得到强制执行，并且正如您所说，让数据库检查完整性从来都不是一个坏主意。 - Yotam Ofek

我同意将数据转换为 json 格式几乎不会有任何损失。但我很好奇，你是否测试过 jsonb 占用的空间是否比 text 更少。如果 text 超过了一定长度（TOAST），它将默认压缩存储。而且 INSERT 和 SELECT 执行时间会稍微长一些，因为需要对该值进行解析或取消解析。 - Laurenz Albe

显然，这非常取决于您要存储什么类型的数据。由于我的JSON对象是字符串列表，存储成本可能与TOASTed文本相当，但对于具有许多大数字的复杂数据类型，存储二进制表示将提供巨大的收益。而且，如果您曾经处理过可能随时间变化的大型数据集，那么您可能已经学到了选择正确的数据表示类型对于清洁的数据库至关重要。你的评论并没有建设性，因为我的问题不是“我应该吗？”，而是“我怎么能？” - Yotam Ofek

我在参考OP的评论：我想利用PG的内置json和jsonb类型更有效地存储对象。但是在这里进行理论讨论是没有意义的。 - Laurenz Albe

我是OP。正如你所说，在这里进行理论讨论没有任何用处。如果你想要实际讨论，这里有一个：我需要能够基于数组的长度进行过滤和索引，我认为我们不需要讨论来证明 jsonb 甚至 json 对于存储具有这种用例的数组更加高效。 - Yotam Ofek

Answer 1

不需要一个 parse_json 列，只需改变列的类型：

ALTER TABLE table_name
ALTER COLUMN json_column TYPE json USING json_column::json;

请注意，如果您计划对这些值进行大量的JSON操作（例如从对象中提取元素、修改对象等），最好使用jsonb。只应将json用于存储JSON数据。此外，正如Laurenz Albe指出的那样，如果您不需要对这些值进行任何JSON操作，并且您不关心postgresql可以对它们执行的验证（例如，因为您相信源始终提供有效的JSON），那么使用text是一个完全有效的选项（或者bytea）。

- redneb

---

非常感谢您及时而且详细的回复！我认为提供一个更通用的关于解析JSON对象的答案会更好，以便将来参考。也就是说，从您的回答中我学到的最重要的一点是，将数据类型转换为JSON类型（::json或::jsonb）是在PG中解析JSON字符串的标准方法。很遗憾似乎没有关于这个特性的文档。 - Yotam Ofek