CODEWITHSUNDEEP
ruby-on-railsjsonpostgresqlactiverecord
leifg
leifg

Reputation: 8988

Rails: Querying Postgres for JSON Array

I am using Rails (4.2) and the Postgres JSON data type to store arrays:

#<Mapping:0x007fa3b6263390 foo: ["bar", baz]>

Now I want to query for all mappings with a specific array:

Mapping.where(foo: ["bar", "baz"])

Turns out this does not work. What query can I use to find what I want? All the JSON query examples are usually for hashes, not for arrays (at least not at the top level).

Upvotes: 0

Views: 684

Answers (1)

Mihai Dinculescu
Mihai Dinculescu

Reputation: 20033

ActiveRecord has built-in support for Postgres arrays.

# equality
Book.where("tags = ARRAY[?]::varchar[]", ["fantasy", "fiction"])

# inclusion
Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])

There are quite a lot of array functions available, check them out here.

Upvotes: 1

Related Questions