How to query nested json attribute on postgres / ruby on rails

Question

My json for an attribute in a record looks as so:

 { foo: bar,   
   foo1: [{key1: value, key2: value},
         {key1: value, key2: value}]
  }

i can query for foo just fine by using

Model.where("attribute->>'foo' = ?", "bar")

Having some trouble querying for values in in key1 and key2

Gabriel Mesquita · Accepted Answer · 2018-01-31 12:06:10Z

2

Take a look here postrgeSQL

You can use this operator #>> to perform what you need, as the link says 'Get JSON object at specified path as text', for example:

'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

Model.where("attribute#>>'{foo1, key1}' = ?", "bar")

You should just adapt to your needs now. Hope it helped!

edited Jan 31, 2018 at 12:06

answered Jan 30, 2018 at 15:45

Gabriel Mesquita

2,4121 gold badge23 silver badges30 bronze badges

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

2 Comments

lezayskincoach Over a year ago

I am still getting back an empty array when querying with this criteria. Does it matter if i have hashes within the array? e.g. your example is "a": [1,2,3], mine would be "a":[{1:2}, {3:4}]

Gabriel Mesquita Over a year ago

Have you tried to use this operator #>, instead ? At the link that I posted it shows an example to retrieve the hash value.

Collectives™ on Stack Overflow

How to query nested json attribute on postgres / ruby on rails

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related