Using JSON in postgres, how do I query a nested array? [duplicate]

Question

I have a few User records with a details json column that look like this:

User.create(details: {accounts: [{name: 'one', id: 1}, {name: 'two', id: 2}])
User.create(details: {accounts: [{name: 'three', id: 3}, {name: 'two', id: 2}])
User.create(details: {accounts: [{name: 'three', id: 3}, {name: 'four', id: 4}])

How do I query for all records where id is 2?

check here stackoverflow.com/questions/22667401/… OR here edgeguides.rubyonrails.org/active_record_postgresql.html#json — LHH
– LHH, Commented Dec 6, 2017 at 12:38

RandallB · Accepted Answer · 2017-12-06 12:45:14Z

0

Thanks to Using jsonb (PostgreSQL), how do I retrieve items with a certain value that's saved as an array? I was put on the right track.

The query to match:

User.where("? in (SELECT json_array_elements(details->'accounts')->>'id')", "2")

answered Dec 6, 2017 at 12:45

RandallB

5,5956 gold badges36 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

Using JSON in postgres, how do I query a nested array? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related