时间戳

首页 > 解决方案 > Sqlalchemy json 数组，在数组内执行类似查询

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

问题描述

我的数据库中有一个表，其中包含一个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'}];

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

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

标签： postgresqlsqlalchemyflask-sqlalchemy

解决方案

是的，你可以通过拆开它来做到这一点：

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")');

推荐阅读