sql - 如何使用 SQL LIKE 运算符在列中搜索精确模式？

Question

我有一个表格，如下所示

    date    |                                                   tags                                                   
------------+----------------------------------------------------------------------------------------------------------
 2018-10-24 | {"table": "bank_trans", "metric": "withdrawal", "location": "UK"}
 2018-10-24 | {"table": "bank_trans", "metric": "balance", "account_id": "477", "location": "ny", "country": "USA"}
 2018-10-24 | {"table": "bank_trans", "metric": "deposit", "location": "blr", "country": "IND"}
 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

如果我想要包含如下搜索模式的特定行

select date, tags
from webhook_forecastmodel
where tags LIKE '%"table": "bank_trans"%' AND
      tags LIKE '%"metric": "balance"%' AND
      tags LIKE '%"account_id": "477"%';

在这种情况下，我得到两个结果

    date    |                                                   tags                                                   
------------+----------------------------------------------------------------------------------------------------------

 2018-10-24 | {"table": "bank_trans", "metric": "balance", "account_id": "477", "location": "ny", "country": "USA"}

 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

我了解 SQL 查询返回模式匹配的行。

但我只想要 LIKE 搜索模式中完全提到的行，即 "table": "bank_trans", "metric": "balance" 和 "account_id": "477" 这让我们只有一行

 2018-11-02 | {"table": "bank_trans", "metric": "balance", "account_id": "477"}

有什么可能的方法可以实现这一目标吗？

Answer 1

更新:这个问题假设一个最新的 Postgres 版本。它不适用于已过时且不再维护的 9.2 版，但我还是将其留在这里以供引用。

---

如评论中所述，不要使用 LIKE，使用 JSON functions .为了能够做到这一点，您必须转换值:

select date, tags
from webhook_forecastmodel
where tags::jsonb @> '{"table": "bank_trans"}'::jsonb 
  AND tags::jsonb @> '{"metric": "balance"}'::jsonb 
  AND tags::jsonb @> '{"account_id": "477"'}::jsonb;

@> 运算符检查左侧的值是否包含右侧的键/值对。

---

上面的代码还会返回包含多个键/值对的行。如果您想要那些完全那些键/值对，请使用=

select date, tags
from webhook_forecastmodel
where tags::jsonb = '{"table": "bank_trans", 
                      "metric": "balance", 
                      "account_id": "477"}'::jsonb;

jsonb 数据类型规范化键/值对，因此键的顺序无关紧要，= 比较将正常工作。

在线示例:https://rextester.com/LYXHUC20162

在在线示例中，tags 列中的键顺序与用于 = 运算符的键顺序不同，以证明 JSONB 规范化了 JSON 表示。

---

鉴于数据的性质，最好将列定义为 jsonb 以避免所有转换。