postgresql - 过滤 json 对象的值

Question

我有一个包含 jsonb 列的表:

CREATE TABLE test(
  id serial primary key,
  data jsonb
);

insert into test (data) values(
({"a" : 1,"b" : 2,"r1" : 2, "r2" : 5}),
({"a" : 3,"b" : 3,"r1" : 2, "r2" : 2,"r3" : 7}),
({"a" : 5,"b" : 1,"r1" : 1, "r2" : 5,"r4" : 2}),
({"a" : 2,"b" : 2,"r1" : 5, "r2" : 6,"r3" : 5,"r4" : 7}),
({"a" : 9,"b" : 2,"r1" : 9, "r2" : 1,"r3" : 5,"r5" : 2}));

然后我要执行下面的

select data 
from test 
where data->>'r1' = 2 or data->>'r2' = 2 or data->>'r3' = 2 or data->'r4' = 2 or data->>'r5' = 2;

有没有更简洁的方式来写这个select？

Answer 1

如果 r 可能大于 5:

select data
  from testX
 where EXISTS (
          SELECT 1
            FROM generate_series(1, 5) AS i
           WHERE data->>('r' || i) = '2');