选择json或jsonb或文本的数据类型 [英] What data type to choose json or jsonb or text

问题描述

我想存储一个大的json散列(或内容，随心所欲地命名)，用大"表示超过1000个键值对，我不想对该json字段进行任何搜索，我只想从数据库中检索它，并将其传递给javascript进行解析并构建可视化结果.

I would like to store a big json hash (or content, call it like you want) and by "big" I mean something above 1000 key value pairs, I don't want to do any search on that json field, I just want to retrieve it from the database and pass it to javascript to parse it and construct a visual result.

在Postgresql上有一个json类型和jsonb类型(也许我也可以使用text字段来存储json).我只是想为此目的做出正确的选择，所以我需要经验丰富的人的一些建议.

On Postgresql there is a json type and jsonb type (and maybe I can use a text field as well to store json) . I just want to make the right choice for that purpose, so I would like some advice from experienced people.

推荐答案

假设您正在谈论的是实际的，严格的JSON(没有诸如无引号的键之类的怪癖)...

Assuming you're talking about actual, strict JSON (without quirks such as unquoted keys)...

json与text并没有太大区别.除了验证JSON 之外，它没有其他用途.

json isn't too different from text. It doesn't do much apart from validating JSON.

jsonb与这两者相比是不同的野兽:它是具有自己内部格式的成熟数据结构，在搜索中具有更多可用操作.例如，json没有适用的=(等于运算符). jsonb有. (text也是如此，即使它在语义上有所不同.)

jsonb is a different beast compared to the these two: it's a full-fledged data structure with its own internal format that has much more operations available in searches. For instance json has no applicable = (equality operator). jsonb has. (text has too, even though it's semantically different.)

索引起来要明智得多，但是必须在读写过程中来回转换.

It's much more sensible to index, but it has to be transformed back and forth during reads and writes.

鉴于此，jsonb在这里看起来不是一个声音选择.

Given that, jsonb doesn't look like a sound choice here.

...所以只剩下一个决定:

... So there is only one decision left to make:

是否要确保您的数据库在列中仅包含有效的JSON值?在数据库级别?还是您信任该数据库的每个客户端(通常是服务器应用程序)仅提供有效数据?

Do you want to ensure that your database contains only valid JSON values in your column? On the database level? Or do you trust every client of that database (server apps, typically) to only supply valid data?

json都是相对安全的选择.由于没有进行验证，理论上使用text可以将性能提高到可以忽略的程度，但是您只能通过基准测试来获得特定的数字.但是它没有针对非JSON值的保护措施，并且客户端中的意外错误可能不会引起注意.负责任地测试！

json is a relatively safe choice either way. Using text could theoretically improve performance by a negligible margin due to absence of validation, but you'll only get specific numbers by benchmarking. But it wouln't have that safeguard against non-JSON values, and an accidental bug in the client could go unnoticed. Test responsibly!

这篇关于选择json或jsonb或文本的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！