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

Question

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

我需要在 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
               }
            }
         ]
      }
   }
]

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+ 中，JSONB 中的递归搜索非常适合 jsonpath 的新功能。

查找包含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")')

阅读文档:

• The SQL/JSON Path Language
• jsonpath Type