如何在JSON Postgres数据类型列中搜索特定字符串？

Question

我有一个名为params的列，存储在名为reports的表中，其中包含JSON。

我需要找出哪些行在JSON数组中包含文本'authVar'。 我不知道文本可能出现的路径或级别。

我只想使用标准的类似运算符搜索JSON。

类似于：

SELECT * FROM reports
WHERE params LIKE '%authVar%'

我已经搜索、谷歌并阅读了Postgres文档。我对JSON数据类型不是很了解，认为自己可能错过了一些容易的东西。

JSON的格式大致如下。

[  
   {  
      "tileId":18811,
      "Params":{  
         "data":[  
            {  
               "name":"Week Ending",
               "color":"#27B5E1",
               "report":"report1",
               "locations":{  
                  "c1":0,
                  "c2":0,
                  "r1":"authVar",
                  "r2":66
               }
            }
         ]
      }
   }
]

- Joshua Dance

Answer 1

在Postgres 11或更早的版本中，可以通过递归遍历未知的json结构，但这会相当复杂且代价高昂。我建议采用暴力法，这应该能很好地解决问题：

select *
from reports
where params::text like '%authVar%';
-- or 
-- where params::text like '%"authVar"%';
-- if you are looking for the exact value

查询非常快，但在搜索字符串是某个键的一部分时，可能会返回意外的额外行。

在 Postgres 12+ 中，使用新功能 jsonpath 对 JSONB 进行递归搜索非常方便。

查找包含 authVar 的字符串值：

select *
from reports
where jsonb_path_exists(params, '$.** ? (@.type() == "string" && @ like_regex "authVar")')

JSON路径：

$.**                     find any value at any level (recursive processing)
?                        where
@.type() == "string"     value is string
&&                       and
@ like_regex "authVar"   value contains 'authVar'

或者查找精确值：

select *
from reports
where jsonb_path_exists(params, '$.** ? (@ == "authVar")')

阅读文档：

• SQL/JSON路径语言
• jsonpath类型

- klin

---

递归遍历的优点是什么？ - Cale Sweeney

本教程详细解释了“params::text”的用法：http://www.postgresqltutorial.com/postgresql-cast/ - Cale Sweeney

@CaleSweeney - 当我们搜索与键中相同的属性时，这种简单的解决方案将会失败。请问需要对应哪种语言？ - klin

@ersu - 是的，请阅读文档中的9.15.2.2. 正则表达式。 - klin

@klin 你知道这两个表达式之间有什么区别吗？ where jsonb_path_exists(params, '$.** ? (@.type() == "string" && @ like_regex "authVar")') 和 where params @? '$.** ? (@.type() == "string" && @ like_regex "string" flag "i")'？ - Marius Lian