Sql Postgres JSONB对象筛选

发布于03月17日

我有一张Postgres表，看起来像这样:

CREATE TABLE products(
   id string,
   attributes jsonb,
   PRIMARY KEY(id)
);

属性字段的示例如下所示:

 {
      "endDate": [
        "2024-02-20T21:00:00.000Z"
      ],
      "countries": [
        "US","IT","ES"
      ],
      "type": [
        "RETAIL"
      ],
      "startDate": [
        "2024-02-13T08:00:00.000Z"
      ],
      "categories": [
        "ELECTRONICS"
      ],
      "currency": [
        "USD"
      ],
      "status": [
        "ACTIVE"
      ]
    }

我需要创建过滤器，作为输入，我可能会得到一个国家的列表，例如["美国"，"MX"]，每个产品有这些国家之一将符合标准和/或startDate可能在某些提供的日期之后. 我还对这种查询的性能感兴趣，因为这个表可能真的很大.

我试过使用此查询按国家/地区筛选产品:

SELECT *
FROM products
WHERE
  (attributes @> '{ "countries": ["US","MX","JP"] }')

但此查询将仅列出所有3个提供国家的产品.我需要至少有一个国家匹配，在这个例子中，它有"美国"，所以它应该是匹配.

id	attributes
1	{"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]}

attributes

{"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]}


Bitmap Heap Scan on public.products (cost=116.51..2432.07 rows=13325 width=396) (actual time=4.505..56.491 rows=9513 loops=1)
Output: id, attributes
Recheck Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" \|\| @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath)
Rows Removed by Index Recheck: 28518
Heap Blocks: exact=2149
-> Bitmap Index Scan on products_attributes_idx (cost=0.00..113.18 rows=13325 width=0) (actual time=4.238..4.239 rows=38031 loops=1)
Index Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" \|\| @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath)
Planning Time: 0.374 ms
Execution Time: 56.930 ms

Bitmap Heap Scan on public.products (cost=116.51..2432.07 rows=13325 width=396) (actual time=4.505..56.491 rows=9513 loops=1)

Output: id, attributes

Recheck Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" || @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath)

Rows Removed by Index Recheck: 28518

Heap Blocks: exact=2149

-> Bitmap Index Scan on products_attributes_idx (cost=0.00..113.18 rows=13325 width=0) (actual time=4.238..4.239 rows=38031 loops=1)

Index Cond: (products.attributes @@ '(exists ($."countries"[*]?(@ == "US" || @ == "MX")) && exists ($."startDate"?(@ > "2024-02-12T07:00:00.000Z")))'::jsonpath)

Planning Time: 0.374 ms

Execution Time: 56.930 ms

Sql Postgres JSONB对象筛选

推荐答案

Sql相关问答推荐

SQL：创建查询以添加减少的总数

如何在PostgreSQL中同时为id s列表执行多个update语句？'

从以前的非空值行中获取值

Stack Exchange站点上的最短帖子(按正文长度计算，用户名为原始发帖(SEDE))

将主表与历史表连接以获取主表的当前汇率以及历史表中的上一个和最后一个汇率

计算周时出现SQL错误结果

将计算列设置为持久化的目的是什么？

根据最大值为字母数字大小写分配数值

使用同一个表，为什么IN、NOT IN、NOT EXISTS和EXISTS有不同的输出？

SQL：使用年/月/日分区查询某个时间段的数据

Select 最频繁的值以及分组依据

Postgresql - WHERE 中的 MAX 标准 - 初学者问题

如何根据 SQL 中的阈值标记一个集群中的所有值？

MySQL中的递归查询邻接表深度优先？

PostgreSQL中如何提取以特定字符开头的字符串中的所有单词？

自动生成计算频率的列

HIVE SQL where 子句未按预期工作

在 Athena / Presto 中提取 JSON 对象以获取动态密钥

postgreSQL 中的循环表

在 SQL 中将行显示为列