如何在JSON Postgres数据类型列中搜索特定的字符串? [英] How do I search for a specific string in a JSON Postgres data type column?

问题描述

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

I have a column named params in a table named reports which contains JSON.

我需要找到JSON数组中任何位置包含文本'authVar'的行.我不知道文字显示的路径或级别.

I need to find which rows contain the text 'authVar' anywhere in the JSON array. I don't know the path or level in which the text could appear.

我只想使用类似operator的标准搜索JSON.

I want to just search through the JSON with a standard like operator.

类似的东西:

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

我已经搜索并搜索过Google，并阅读了Postgres文档.我不太了解JSON数据类型，并且发现缺少一些简单的东西.

I have searched and googled and read the Postgres docs. I don't understand the JSON data type very well, and figure I am missing something easy.

JSON看起来像这样.

The JSON looks something like this.

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

推荐答案

可以递归地遍历未知的json结构，但这会相当复杂且成本很高.我会建议使用蛮力方法，该方法应该能很好地工作:

It is possible to recursively walk through an unknown json structure, but it would be rather complex and costly. I would propose the brute force method which should work well:

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

查询速度非常快，但是如果搜索的字符串是键之一的一部分，则查询可能会返回意外的额外行.

The query is very fast but may return unexpected extra rows in cases when the searched string is a part of one of the keys.

在 Postgres 12 中，JSONB中的递归搜索对jsonpath的新功能非常满意.

In Postgres 12 the recursive searching in JSONB is pretty comfortable with the new feature of jsonpath.

查找包含 authVar 的字符串值:

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

jsonpath:

$.**                     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类型

• The SQL/JSON Path Language
• jsonpath Type

这篇关于如何在JSON Postgres数据类型列中搜索特定的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！