postgresql 需要帮助创建一个SQL查询，查看json对象内部并将变量传递到搜索字符串中

ux6nzvsh 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(1)|浏览(119)

我有一个名为hobbies的数据库和一个名为games的表。在games表中，我有一个名为titles的字段。在titles字段中是一个json对象。

[{
    "titles": ["Double Dragon", "TecmoBowl"]
}]

SQL查询

select *
from hobbies.games
where  jsonb_path_exists(titles, '$.titles[*] ? (@ == "{{ titles }}")');

{{ titles }}是web表单中变量的格式。当执行查询时，我得到以下错误。
查询错误：错误：函数jsonb_path_exists（jsonb，text）不存在。
我的问题是我不能在这个查询中使用绑定变量而不使请求无效。
我也试过这个例子，但没有成功。

select
from hobbies.games
WHERE jsonb_path_exists(titles, '$.** ? (@.type() == "string" && @ like_regex "{{ 
titles }}")')

如果我用标题“双龙”替换绑定变量，它会像预期的那样工作，但变量的大括号似乎使我的请求无效。
如何将一个变量从web表单传递到一个动态搜索json对象的sql查询中，并且仍然满足jsonb_path_exists命令的字符串要求？
希望你能理解。
感谢您提供的任何帮助。

1条答案

我构建了一个系统来实现这一点，但不是直接从webform传递到SQL，而是先使用jinja渲染SQL，然后传递SQL。这为您提供了更好的灵活性，并可以控制您可以动态生成的SQL类型。
在你的例子中，我的webform仍然会产生一个标题列表，但我会使用一个像这样的jinja模板：

select *
from hobbies.games
where title in(
{%- for t in titles %}
'{{ t }}'{{ ', ' if not loop.last else '' }}
{%- endfor -%}
)

这将呈现为，

select *
from hobbies.games
where title in(
'Double Dragon',
'TecmoBowl')

你可以在here上试试，看看我的意思。