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!

It might help you: nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails — Jayaprakash
– Jayaprakash, Commented Oct 6, 2016 at 12:10

Andrey Deineko · Accepted Answer · 2016-10-07 21:17:17Z

18

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)

edited Oct 7, 2016 at 21:17

answered Oct 6, 2016 at 12:11

Andrey Deineko

52.5k11 gold badges119 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Anton Maminov Over a year ago

@> is jsonb operators. Unfortunately, I have json.

Anton Maminov Over a year ago

I've tried, but it's not working. It alway returns an empty result.

Andrey Deineko Over a year ago

@mamantoha how come? It is the way you query json pg column. What's the issue with that?

Anton Maminov Over a year ago

I think the problem is array of hashes.

Anton Maminov Over a year ago

Anyway, thank you for your help. I migrate my data from json to jsonb. And this works for me User.where('locations @> ?', [{ kind: 'office' }].to_json)

|

sequielo · Accepted Answer · 2018-12-17 21:12:29Z

6

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)

answered Dec 17, 2018 at 21:12

sequielo

1,6411 gold badge20 silver badges27 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 29, 2019 at 22:43

Jonatas Eduardo

87410 silver badges18 bronze badges

Comments

Harsimar Sandhu · Accepted Answer · 2018-08-06 10:18:06Z

3

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

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

answered Aug 6, 2018 at 10:18

Harsimar Sandhu

1176 bronze badges

Comments

refeals · Accepted Answer · 2018-04-03 15:37:33Z

2

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.

answered Apr 3, 2018 at 15:37

refeals

811 silver badge5 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

9 Comments

Comments

Filters based on the values of objects inside array JSONB

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

Comments

Filters based on the values of objects inside array JSONB

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related