Rails query interface: selecting rows in database where any of the JSON array's values match a certain criteria

Question

Database info:

• Database: PostgresSQL

• Table name: publishing_rules

• Column name: menu_items

• Column format: JSON

• Example column value: {"items":[{"id":1,"title":"dfgdfg"},{"id":2,"title":"sdf"}]}

I need to gather all columns which have at least one item with an id equal to my value. So far I've come up with this:

    id = 1
    items = PublishingRule.where("menu_items #> '{items,0}' ->> 'id' = ?", id.to_s)

However this code only acquires columns with items array first value matching my criteria. I need to modify my code to something similar to:

    items = PublishingRule.where("menu_items #> '{items, ANY}' ->> 'id' = ?", id.to_s)

or

    id = 1
    items = PublishingRule.where("menu_items #> '{items.map}' ->> 'id' = ?", id.to_s)

How do I do that?

Answer 1

Since the items is array at given example you can't work it out using only operators. You need to use jsonb_array_elements() in order to look into that.

Here's SQL query example to meet your requirement:

SELECT  *
FROM    publishing_rules
WHERE   EXISTS (
            SELECT  1
            FROM    jsonb_array_elements( menu_items -> 'items' )
            WHERE   value ->> 'id' = '2'
            LIMIT   1
        );

So, using within WHERE EXISTS lookup into the array does the trick.