arrays - 如何改进或索引 postgresql 的 jsonb 数组字段?
问题描述
我通常使用 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;
但是如果我想用createdate或bonus或lottorry或product查询 jsonb 数组中的对象,我应该使用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;
Thie jsonb 索引看起来很有用,但它在不同行之间看起来很有用,而且我的操作主要在一行的一个 jsonb 字段中工作。
我非常担心效率,对于存储在一行的一个 jsonb 字段中的数百万个对象,这是个好主意吗?以及如何提高这种场景下的效率?特别是对于查询。
解决方案
你担心是对的。使用像这样巨大的 JSON,您将永远无法获得良好的性能。
您的数据根本不需要 JSON。创建一个存储单个条形码并具有对customers
. 那么一切都会变得简单而高效。
从这个论坛的问题来看,在数据库中使用 JSON 几乎总是错误的选择。
推荐阅读
- rust - 如何修复 Rust 中的“无法编译 `user32-sys”错误
- c - 如何修复“调试断言失败!表达式 file_name != nullptr' 错误?
- java - java.lang.NullPointerException 由于在模拟方法时未在 Mockito 中加载属性而发生
- angularjs - 如何为 ng-model 赋值?
- deep-learning - 如果有两个需要运行的 gpu 作业,JetsonTX2 Nvidia gpu 如何使用?
- php - 基于角色的 Laravel 重定向 - spatie/laravel-permission
- cuda - 启动许多内核 (>1000) 时 CUDA 流被阻止
- twitter-bootstrap-3 - Bootstrap 3:只有一种模式有效
- php - 可以向excel导入添加多个参数吗?
- .net - 如何使用 F# 实现归并排序