CODEWITHSUNDEEP
ruby-on-railsjsonrubypostgresql
Anton Maminov
Anton Maminov

Reputation: 532

How do I search within an JSON array of hashes by hash values?

I am using Postgres' JSON data type to store some information.

For example I have model User with a field locations that holds a json document(array of objects containing pairs of keys and values) in the following format:

[{"name": "Location 1", kind: "house"},
 {"name": "Location 2", kind: "house"},
 {"name": "Location 3", kind: "office"},
 ...
 {"name": "Location X", kind: "house"}
]

I want to query with .where on the JSON data type.

I want to query for users that have at least one location with kind = office.

Thanks!

Upvotes: 12

Views: 11621

Answers (5)

Jonatas Eduardo
Jonatas Eduardo

Reputation: 874

Filters based on the values of objects inside array JSONB

If you want to make filters based on the values of the objects (jsonb) inside the arrays in Rails with postgres you can use something like example below.

Assuming you have a Product model:

Product.select("*").from(
  Product.select("*, jsonb_array_elements(registers) as register")
).where("(register ->> 'price')::numeric >= 1.50")

In above example we select all products with price greater than or equal 1.50.

For this we use:

I applied ident in sub-query just for best readability.

After you can put this in a scope or use other best practice.

Upvotes: 4

sequielo
sequielo

Reputation: 1631

I have used a mix of above answers. This is my working code:

User.where("locations::jsonb @> ?", [{kind: 'office'}].to_json)

As a note, locations is a JSON column of User table (not JSONB datatype)

Upvotes: 6

Harsimar Sandhu
Harsimar Sandhu

Reputation: 117

Source: https://www.postgresql.org/docs/current/static/functions-json.html

User.where("name::jsonb -> 'location' ->> 'kind' = ?", 'office')

Upvotes: 3

refeals
refeals

Reputation: 81

Just to give my two cents:

I have a model with a JSON with a key and an array inside, so it looks like this:

ModelObj: attribute: { key: [val0, val1, val2, val3] }

I needed to find all objects with, for example, attributes where the key has val3 in the array. I changed my attribute type to jsonb and this was how I find it:

Model.where('attribute @> ?', {key: ['val3']}.to_json)

Maybe it's useful to someone out there.

Upvotes: 2

Andrey Deineko
Andrey Deineko

Reputation: 52357

I want to query for users that have locations with kind office

# if locations is jsonb type
User.where('locations @> ?', { kind: 'office' }.to_json)
# if locations is json type
User.where("locations->>'kind' = 'office'")
# if locations is array of hashes
User.where('locations @> ?', [{ kind: 'office' }].to_json)

Upvotes: 18

Related Questions