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

Question

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!

Answer 1

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)

Answer 2

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)

Answer 3

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:

• jsonb_array_elements() from postgres;
• an auxiliary column and alias;
• and methods from ActiveRecord from Rails.

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

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

Answer 4

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

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

Answer 5

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.