无论键如何，按值查询postgres jsonb [英] Query postgres jsonb by value regardless of keys

问题描述

我想获取所有记录，其中jsonb字段中的任何 value 均包含文本，而与键无关.

I want to get all records where any value in jsonb field contains a text regardless of key.

例如:字段包含json {k1: 'hello', k2: 'world'}.我想通过文本"hello"获得此记录.我不在乎键或任何json结构，只在乎值.

For example: field contains json {k1: 'hello', k2: 'world'}. I want to get this record by the text 'hello'. I don't care about keys or any json structure, only values.

一个肮脏的技巧是将字段强制转换为varchar where jsonb_field::varchar like ...，但这很丑陋，它将匹配键和{}.

One dirty hack is to cast the field to varchar where jsonb_field::varchar like ..., but this is ugly and it will match keys and {} as well.

另一个肮脏的骇客看起来像这样:

Another dirty hack looks like this:

SELECT * FROM mytable AS m1 WHERE (
  SELECT string_agg(value, '') FROM jsonb_each_text( (SELECT name FROM mytable AS m2 WHERE m1.id= m2.id)::jsonb ) 
) LIKE '%whatever%';

但这也很丑.

我该怎么做?

推荐答案

对于简单的JSON，您可以使用更合适的查询，例如

For simple JSONs you can use more appropriate query like

select * 
from mytable t 
where exists (
  select 1 
  from jsonb_each_text(t.jsonbfield) j 
  where j.value = 'hello');

它对您的示例中的JSON正常工作，但对{"a":"hello","b":1,"c":{"c":"world"}}

It works fine for JSONs like in your example but not helps for more complex JSONs like {"a":"hello","b":1,"c":{"c":"world"}}

我可以建议像这样创建存储功能

I can propose to create the stored function like

create or replace function jsonb_enum_values(in jsonb) returns setof varchar as $$
begin
  case jsonb_typeof($1)
    when 'object' then
      return query select jsonb_enum_values(j.value) from jsonb_each($1) j;
    when 'array' then
      return query select jsonb_enum_values(a) from jsonb_array_elements($1) as a;
    else
      return next $1::varchar;
  end case;
end
$$ language plpgsql immutable;

列出所有值，包括递归对象(由您决定如何处理数组).

to list all values including recursive objects (It is up to you what to do with arrays).

这是用法示例:

with t(x) as (
  values
    ('{"a":"hello","b":"world","c":1,"d":{"e":"win","f":"amp"}}'::jsonb),
    ('{"a":"foo","b":"world","c":2}'),
    ('{"a":[{"b":"win"},{"c":"amp"},"hello"]}'),
    ('[{"a":"win"}]'),
    ('["win","amp"]'))
select * 
from t 
where exists (
  select *
  from jsonb_enum_values(t.x) j(x) 
  where j.x = '"win"');

请注意，在字符串值前后用双引号引起来.

Note that doublequotes around the string value.

这篇关于无论键如何，按值查询postgres jsonb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！