如何改进或索引postgresql的jsonb数组字段？

Question

我通常使用jsonb字段存储数组数据。例如，我想要存储客户的条形码信息，我将创建一个这样的表:

create table customers(fcustomerid bigint, fcodes jsonb);

一个客户有一行，所有条形码信息存储在fcodes字段中，如下所示:

[
{
"barcode":"000000001",
"codeid":1,
"product":"Coca Cola",
"createdate":"2021-01-19",
"lottorry":true,
"lottdate":"2021-01-20",
"bonus":50
},
{
"barcode":"000000002",
"codeid":2,
"product":"Coca Cola",
"createdate":"2021-01-19",
"lottorry":false,
"lottdate":"",
"bonus":0
}
...
{
"barcode":"000500000",
"codeid":500000,
"product":"Pepsi Cola",
"createdate":"2021-01-19",
"lottorry":false,
"lottdate":"",
"bonus":0
}
]

jsonb数组可能存储数百万具有相同结构的条形码对象。也许这不是一个好主意，但是你知道当我有数千个客户时，我可以将所有的数据存储在一个表中，一个客户在这个表中有一行，所有的数据存储在一个字段中，它看起来非常简洁，易于管理。

对于这种应用场景，如何高效地插入、修改或查询数据?

我可以使用jsonb_insert插入一个对象，就像:

update customers 
set fcodes=jsonb_insert(fcodes,'{-1}','{...}'::jsonb) 
where fcustomerid=999;

当我想修改一些对象时，我发现这有点困难，我应该先知道对象的索引，如果我使用增量键codeid作为数组索引，事情看起来很容易。可以使用jsonb_modify，如下所示:

update customers 
set fcodes=jsonb_set(fcodes,concat('{',(mycodeid-1)::text,',lottery}'),'true'::jsonb) 
where fcustomerid=999;

但是如果我想查询jsonb数组中的对象createdate或bonus或lotttry或product，我应该使用jsonpath操作符。就像:

select jsonb_path_query_array(fcodes,'$ ? (product=="Pepsi Cola")' 
from customer 
where fcustomerid=999;

或像:

select jsonb_path_query_array(fcodes,'$ ? (lottdate.datetime()>="2021-01-01".datetime() && lottdate.datetime()<="2021-01-31".datetime())' 
from customer 
where fcustomerid=999;

jsonb索引看起来很有用，但它在不同的行之间看起来很有用，我的操作主要是在一行的一个jsonb字段中工作。

我非常担心的效率，数百万的对象存储在一行的一个jsonb字段，这是一个好主意吗?在这种情况下如何提高效率呢?特别是对于查询。

Answer 1

你的担心是对的。对于这样一个庞大的JSON，你永远不会得到好的性能。

你的数据根本不需要JSON。创建一个表，存储一个条形码，并有一个外键引用到customers。那么一切都会变得简单而高效。

在数据库中使用JSON几乎总是错误的选择，从这个论坛的问题判断。