使用jsonb(PostgreSQL)，如何检索具有确定值并保存为数组的项目?

问题描述

我正在尝试查找某个类别中的所有配方，其中键/值数据记录在PostgreSQL的jsonb列中(并且值保存为数组).

I'm trying to find all recipes in a certain category, where the key/value data is recorded in a PostgreSQL's jsonb column (and the value is saved as an array).

例如，假设我在食谱"表中将数据"作为我的jsonb列，并且我有三个类似的条目:

For example, say I have "data" as my jsonb column in the "recipes" table, and I have three entries like so:

"Recipe 1" contains a "data" with key/value of: "category_ids"=>[123, 4059]
"Recipe 2" contains a "data" with key/value of: "category_ids"=>[405, 543]
"Recipe 3" contains a "data" with key/value of: "category_ids"=>[567, 982]

我只想检索包含"405"类别ID值的项目(即，仅上面的配方2)

I want to retrieve only items that contain the "405" category id value (i.e. only Recipe 2 above)

到目前为止，这是我使用Ruby on Rails查询PostgreSQL数据库的内容:

This is what I have so far using Ruby on Rails to query the PostgreSQL database:

Recipe.where("(data ->> 'category_ids') LIKE '%405%'")

但是，它同时检索配方1"和配方2"，因为它们都包含"405"文本.

However, that retrieves both "Recipe 1" and "Recipe 2" because they both contain "405" text.

我应该使用哪种查询来正确检索仅具有"405"类别ID的食谱?

What query should I use to correctly retrieve recipes with the "405" category id only?

推荐答案

您还可以直接将IN与json_array_elements一起使用:

You can also directly use IN along with json_array_elements:

Recipe.where("'405' IN (SELECT json_array_elements(data->'category_ids')::text)")

如果您的列是jsonb列，则可以类似地执行以下操作:

And if your column is a jsonb column, you can similarly do:

Recipe.where("'405' IN (SELECT jsonb_array_elements(data->'category_ids')::text)")

这篇关于使用jsonb(PostgreSQL)，如何检索具有确定值并保存为数组的项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！