Using jsonb (PostgreSQL), how do I retrieve items with a certain value that's saved as an array?

Question

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).

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]

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

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

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

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

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

score 2 · Accepted Answer · edited May 10 '16 at 14:04

2

You can also directly use IN along with json_array_elements:

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

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

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

edited May 10 '16 at 14:04

sjsc

4,552
10
39
55

answered May 10 '16 at 07:40

Ezequiel Tolnay

4,302
1
19
28

Thanks, Ziggy. i got this error: ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: function json_array_elements(jsonb) does not exist My postgresql version is 9.5 (using Rails version 4.2.6). Do you know if I have to enable this feature in psql? – sjsc May 10 '16 at 11:46
1

I think I know now :) I did this and it works: Recipe.where("'405' IN (SELECT jsonb_array_elements(data->'category_ids')::text)") So instead of "json_array_elements", I used "jsonb_array_elements" because of the jsonb column. Thank you so much!! – sjsc May 10 '16 at 11:48

score 1 · Answer 2 · answered May 09 '16 at 20:07

1

you need ANY function. Something like

select * from t where '405' = any (
  ARRAY(
    SELECT e::text FROM json_array_elements(data->'category_ids') e
  )
);

or

select * from t where '405' = any (
  ARRAY(
    SELECT e::text FROM jsonb_array_elements(data->'category_ids') e
  )
);

if your type is jsonb (higher than 9.3 postgres version)

http://sqlfiddle.com/#!15/1a895/1

answered May 09 '16 at 20:07

cur4so

1,750
4
20
28

Thanks so much cur4so :) I don't understand PostgreSQL too much so I used Ziggy's solution above that includes the Rails way of doing it. The jsonb_array_elements(data->'category_ids') is key so thank you so much. This was the query output that worked for me after doing the Rails command, which is very similar to yours: SELECT \"recipes\".* FROM \"recipes\" WHERE ('405' IN (SELECT jsonb_array_elements(data->'category_ids')::text))" – sjsc May 10 '16 at 11:57

score 0 · Answer 3 · answered May 09 '16 at 19:23

0

You can use unnest function available. More details can be check here : Postgresql Functions & Operators.

answered May 09 '16 at 19:23

Muhammad Ali

2,173
15
20

Using jsonb (PostgreSQL), how do I retrieve items with a certain value that's saved as an array?

3 Answers3

Linked