Postgres使用嵌套数组查询/过滤JSONB

Question

以下是我的样本需求

我想要满足以下所有条件的客户

1. 在国内"xyz"；，成立于2019年至2021年
2. 应该至少有一个余额在10000到13000之间的账户；abc"；交易日期介于20200110年至20210625年之间。它被格式化并存储为数字
3. 应该在状态"0"中具有至少一个地址；状态1"；引脚代码介于625001和625015之间

下面是表结构

CREATE TABLE customer_search_ms.customer
(
customer_id integer,
customer_details jsonb
)

表中可能有数百万行。我已经在customer_details列上创建了jsonn_ops类型的GIN索引，因为我们还将检查存在条件和范围比较

以下是customer_data JSONB列中的示例数据

customer_id : 1
{
"customer_data": {
"name": "abc",
"incorporated_year": 2020,
"country":"xyz",
"account_details": [
{
"transaction_dates": [
20180125, 20190125, 20200125,20200525
],
"account_id": 1016084,
"account_balance": 2000,
"account_branch": "xyz"
},
{
"transaction_dates": [
20180125, 20190125, 20200125
],
"account_id": 1016087,
"account_balance": 12010,
"account_branch": "abc"
}
],
"address": [
{
"address_id": 24739,
"door_no": 4686467,
"street_name":"street1",
"city": "city1",
"state": "state1",
"pin_code": 625001
},
{
"address_id": 24730,
"door_no": 4686442,
"street_name":"street2",
"city": "city1",
"state": "state1",
"pin_code": 625014
}
]
}
}

现在我为上面写的查询是

SELECT  c.customer_id,
c.customer_details
FROM customer_search_ms.customer c
WHERE c.customer_details @@ CAST('$.customer_data.country ==  "xyz" && $.customer_data.incorporated_year >= 2019 && $.customer_data.incorporated_year <= 2021 ' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.account_details[*] ? (@.account_balance >=  10000) ? (@.account_balance <=  13000) ?(@.account_branch ==  "abc") ? (@.transaction_dates >=  20200110) ? (@.transaction_dates <=  20210625)' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.address[*] ? (@.state ==  "state1") ? (@.pin_code >=  625001) ? (@.pin_code <= 625015)  ' AS JSONPATH)

处理以上场景是最好的写作方式。是否可以将所有3个标准(客户/帐户/地址(组合为一个表达式？该表将有数百万行。我认为将其作为一种表达方式，并点击DB将获得最佳表现。是否可以将这三个条件组合为一个表达式

Answer 1

您的查询没有显示您报告的错误。相反，它运行，但确实给出了"；错误的"；结果与您想要的结果进行比较。它有几个错误，不是语法错误，只是给出了错误的结果。

你的第一个jsonpath看起来不错。它是一个布尔表达式，@@检查该表达式是否产生true。

您的第二个jsonpath有两个问题。它会生成一个与您的条件相匹配的对象列表。但是对象不是布尔值，所以@@将不满意并返回SQL NULL，这在这里被视为false。相反，您需要测试该列表是否为空。这就是@?的作用，所以使用它而不是@@。此外，您的日期存储为8位整数，但您将它们与8个字符的字符串进行比较。在jsonpath中，这样的跨类型比较会产生jsonnull，这里将其视为false。因此，您要么需要将存储更改为字符串，要么将与之比较的文字更改为整数。

您的第三个jsonpath也有@@问题。它的类型问题正好相反，您将pin_code存储为字符串，但您正在针对整数进行测试。最后，"pin_code"出现了一次拼写错误。