jsonb vs jsonb []为客户提供多个地址 [英] jsonb vs jsonb[] for multiple addresses for a customer

问题描述

在PostgreSQL的jsonb字段中保存多个地址是个好主意.我是nosql的新手，我想测试PostgreSQL来做到这一点.我不想有另一个带有地址的表，我更喜欢在同一个表中. 但我对此表示怀疑，我已经看到PostreSQL具有jsonb和jsonb [].

It's a good idea to save multiple addresses in a jsonb field in PostgreSQL. I'm new in nosql and I'd like to test PostgreSQL to do that. I don't want to have another table with addresses, I prefer to have it in the same table. But I'm in doubt, I've seen PostreSQL have jsonb and jsonb[].

哪种地址存储多个地址更好? 如果我使用jsonb，我认为我必须为每个字段添加一个前缀，如下所示:

Which one is better to store multiple addresses? If I use jsonb, I think I must to add a prefix for every field like this:

"1_adresse_line-1"
"1_adresse_line-2"
"1_postalcode"

"2_adresse_line-1"
"2_adresse_line-2"
"2_postalcode"

"3_adresse_line-1"
"3_adresse_line-2"
"3_postalcode"

等

使用jsonb []更好吗?如何工作?

Is it better to use jsonb[], how does it work?

推荐答案

使用具有以下结构的jsonb(而不是jsonb []！)列:

Use a jsonb (not jsonb[]!) column with the structure like this:

select
'[{
        "adresse_line-1": "a11",
        "adresse_line-2": "a12",
        "postalcode": "code1"
    },
    {
        "adresse_line-1": "a21",
        "adresse_line-2": "a22",
        "postalcode": "code2"
    }
]'::jsonb;

尽管，与主表相关的常规表是更好的选择.

Though, a regular table related to the main one is a better option.

为什么不使用jsonb []?看看 JSON定义:

Why not jsonb[]? Take a look at JSON definition:

JSON建立在两个结构上:

JSON is built on two structures:

• 名称/值对的集合.在各种语言中，这都是作为对象，记录，结构，字典，哈希表，键列表或关联数组实现的.
• 值的有序列表.在大多数语言中，这是通过数组，向量，列表或序列来实现的.

• A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.
• An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

因此，您可以在jsonb列中存储对象数组.尝试使用jsonb数组可能是由于对此类数据的误解.我从未见过有这样的解决方案的合理需求.

In a jsonb column you can therefore store an array of objects. Attempts to use the array of jsonb are probably due to misunderstanding of this type of data. I have never seen a reasonable need for such a solution.

这篇关于jsonb vs jsonb []为客户提供多个地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！