JSONB query in Rails for a key that contains an array of hashes

Question

I have a Rails 5 project with a Page model that has a JSONB column content. So the structure looks like this (reduced to the bare minimum for the question):

#<Page id: 46, content: {..., "media" => [{ "resource_id" => 143, "other_key" => "value", ...}, ...], ...}>

How would I write a query to find all pages that have a resource_id of some desired number under the media key of the content JSONB column? This was an attempt that I made which doesn't work (I think because there are other key/value pairs in each item of the array): Page.where("content -> 'media' @> ?", {resource_id: '143'}.to_json)

EDIT: This works, but will only check the first hash in the media array: Page.where("content -> 'media' -> 0 ->> 'resource_id' = ?", '143')

Answer 1

Using sql, this should give you all pages which have resource id 143:

select * from pages p where '{"resource_id": 143}' <@ ANY ( ARRAY(select jsonb_array_elements ( content -> 'media' ) from pages where id=p.id ) );

Postgresql has a function called ANY (postgres docs) which uses the form expression operator ANY (array). The left-hand expression is evaluated and compared to each element of the array using the given operator.

Since the right hand side parameter to ANY has to be an array (not a json array), we use the jsonb_array_elements method to convert the content->media json array into a set of rows which are then converted into an array by using ARRAY().

The <@ operator checks if the expression on the right contains the expression on the left side. Ex: '{"a": 1}'::jsonb <@ '{"b": 2, "a": 1}'::jsonb will return true.