postgresql - Sqlalchemy json 数组，在数组中执行类似查询

Question

我的数据库中有一个表，其中包含一个 form 列，该列具有 JSONb 数据类型，大部分时间的数据是这样的:

id = 1 
form = [{'label': 'x1', 'value': True},
 {'label': 'x2', 'value': 'v1'},
 {'label': 'x3', 'value': 'c2'}];
id = 2 
form = [{'label': 'x1', 'value': True},
 {'label': 'x2', 'value': 'v21'},
 {'label': 'x3', 'value': 'v22'}];

我想知道是否可以在该表中搜索以查找在其 form 字段中的元素中包含 c% 文本的行？即在上述情况下，我想获得 id =1 的实体；

在 sqlalchemy 中没有必要这样做，但如果可能的话，这是首选的解决方案；

Answer 1

是的，您可以通过拆分来做到这一点:

select id 
from mytable
join lateral (select jsonb_array_elements(form) as elements) as sub1 on true
join lateral (select key, value from jsonb_each_text(elements)) as sub2 on true
WHERE key = 'value' and value like 'c%';
 id
----
  1

第一个横向连接将数组的每一项放入其自己的行中。第二个横向连接将对象的每个键值对放入其自己的行中。然后，我们只查找 key = 'value' 和 value 为 'c%' 的行。如果您还想搜索值为“c%”的标签，只需删除 key = 'value' 部分即可。

编辑:这在带有 json_path 的 postgres 12 中会更容易:

select id 
from mytable 
where jsonb_path_exists(form, '$.**.value ? (@ starts with "c")');