Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ruby on Rails-5 postgresql-9.6 jsonb queries
http://stackoverflow.com/questions/22667401/postgres-json-data-type-rails-query
http://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails
#payload: [{"kind"=>"person"}]
Segment.where("payload @> ?", [{kind: "person"}].to_json)
#data: {"interest"=>["music", "movies", "programming"]}
Segment.where("data @> ?", {"interest": ["music", "movies", "programming"]}.to_json)
Segment.where("data #>> '{interest, 1}' = 'movies' ")
Segment.where("jsonb_array_length(data->'interest') > 1")
Segment.where("data->'interest' ? :value", value: "movies")
Segment.where("data -> 'interest' ? :value", value: ['programming'])
data: {"customers"=>[{:name=>"david"}]}
Segment.where("data #> '{customers,0}' ->> 'name' = 'david' ")
Segment.where("data @> ?", {"customers": [{"name": "david"}]}.to_json)
Segment.where("data -> 'customers' @> '[{\"name\": \"david\"}]'")
Segment.where(" data -> 'customers' @> ?", [{name: "david"}].to_json)
#data: {"uid"=>"5", "blog"=>"recode"}
Segment.where("data @> ?", {uid: '5'}.to_json)
Segment.where("data ->> 'blog' = 'recode'")
Segment.where("data ->> 'blog' = ?", "recode")
Segment.where("data ? :key", :key => 'uid')
Segment.where("data -> :key LIKE :value", :key => 'blog, :value => "%recode%")
#tags: ["dele, jones", "solomon"]
# get a single tag
#Segment.where("'solomon' = ANY (tags)")
# which segments are tagged with 'solomon'
Segment.where('? = ANY (tags)', 'solomon')
# which segments are not tagged with 'solomon'
Segment.where('? != ALL (tags)', 'solomon')
# or
Segment.where('NOT (? = ANY (tags))', 'solomon')
#multiple tags
Segment.where("tags @> ARRAY[?]::varchar[]", ["dele, jones", "solomon"])
# tags with 3 items
Segment.where("array_length(tags, 1) >= 3")
# SUM (Thanks @skplunkerin)
https://gist.github.com/mankind/1802dbb64fc24be33d434d593afd6221#gistcomment-2711098
`https://stackoverflow.com/a/39280048/1180523`
```
#data: [{"amount"=>12.0},{"amount"=>25.50},{"amount"=>17.99}]
Segment.select("SUM((data ->> 'amount')::FLOAT) AS total_amount")
```
@charlesemarsh

This comment has been minimized.

Copy link

@charlesemarsh charlesemarsh commented Feb 2, 2018

Thanks for this! Saved me evening...

@neymarsabin

This comment has been minimized.

Copy link

@neymarsabin neymarsabin commented Jun 20, 2018

Awesome. 👍

@bramj

This comment has been minimized.

Copy link

@bramj bramj commented Jul 5, 2018

Great stuff, thanks!

@skplunkerin

This comment has been minimized.

Copy link

@skplunkerin skplunkerin commented Sep 19, 2018

SUM

https://stackoverflow.com/a/39280048/1180523

#data: [{"amount"=>12.0},{"amount"=>25.50},{"amount"=>17.99}]
Segment.select("SUM((data ->> 'amount')::FLOAT) AS total_amount")
@jimmychu0807

This comment has been minimized.

Copy link

@jimmychu0807 jimmychu0807 commented Jan 19, 2019

Thanks for the note.

@NirAviel

This comment has been minimized.

Copy link

@NirAviel NirAviel commented Feb 10, 2019

Hi,
I have a simple jsonb column but always getting an empty results...
column data (flags) - {home_page: '1'} (also tried {home_page: 'true'})
RoR -
scope :home_page, -> { where("flags ->> 'home_page' = ?", "1") }
where("flags @> ?", {home_page:'1'}.to_json)
where("flags ->> 'home_page' = '1'")

I see the value in the db,
Model.home_page return the right value (1...)

rails 5.2.2
postgresql 10.3

help... :(

@Mansh05

This comment has been minimized.

Copy link

@Mansh05 Mansh05 commented Aug 18, 2019

#payload: [{"kind"=>"person", "weight": 200}]
if Payload is like above
Segment.where("payload @> ?", [{kind: "person"}].to_json) will return null.

I tried doing it Segment.where("payload <@ ARRAY[?]::jsonb[]", [{kind: "person"}].to_json) gives an empty result.
[]
but to compare it requires the exact structure of the object hash.
Segment.where("payload <@ ARRAY[?]::jsonb[]", [{kind: "person", "weight": 200}].to_json) will return the result.

How do i query by only providing the kind attribute in the query?

@mankind

This comment has been minimized.

Copy link
Owner Author

@mankind mankind commented Aug 22, 2019

@Mansh05,
I don't have an immediate answer but I hope this links help:
https://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails
https://stackoverflow.com/questions/28486192/postgresql-query-array-of-objects-in-jsonb-field

Please post your solution here to help others who stumble on this gist in future.

@Mansh05

This comment has been minimized.

Copy link

@Mansh05 Mansh05 commented Aug 22, 2019

@mankind, Thank you for redirects. I changed my data structure a bit just to make it work.

Now my data structure is like this
filters: { data: ["10", "20", "30"] }
filter is a column a the table with type jsonb.
With this i had to solve an or match query so that what ever is passed, at least on of the values should be there.

example: filter that are requested are ["20", "40"].

so two ways

  1. This one will look for all the values contained in the database like given above.
    segment.where("filters @> ?", {data: ["20", "40"]})
    The above will return null as there is no 40 in it. If we had ["20", "30"] than we could get one value.

  2. If we want to retrun the value even if only one is there than we can do
    segment.where("(filters -> :key)::jsonb ?| ARRAY [:value]", key: 'data', value: ['20', '40'])
    This will return the value if there is atleast on present in the database.

The structure like {key: 'a', display: 'trial'} is not a good structure to keep as we have to pluck or select each value of a key to another array and query there. This will bring n*2 query which is bad.

So my suggestion after running a performance testing is that whenever we save the data to jsonb object, parse it in such a way that we just need to compare the real query that would come and save it .

Example:
My real data stuct is
values: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}]
Than in your db save it like this.

Table displayers
columns: value:jsonb array: true, filter:jsonb
1 row- value: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}], filter: { keys: ['a', 'b'] }

Now you can query displayers.filter -> keys with

displayers.where("(filter -> :key)::jsonb ?| ARRAY [:value]", key: 'keys', value: ['a', 'c'])
this will retrun row 1.

I cannot write this in stackoverflow as i m not allowed to write an answer.

Thanks and please let me know if this is not clear.

@GesJeremie

This comment has been minimized.

Copy link

@GesJeremie GesJeremie commented Jan 5, 2020

Thanks! You saved me a lot of time :)

@silverdr

This comment has been minimized.

Copy link

@silverdr silverdr commented Nov 1, 2020

FWIW - nested structure query examples would also be helpful

@valachi

This comment has been minimized.

Copy link

@valachi valachi commented Feb 25, 2021

@mankind, Thank you for redirects. I changed my data structure a bit just to make it work.

Now my data structure is like this
filters: { data: ["10", "20", "30"] }
filter is a column a the table with type jsonb.
With this i had to solve an or match query so that what ever is passed, at least on of the values should be there.

example: filter that are requested are ["20", "40"].

so two ways

  1. This one will look for all the values contained in the database like given above.
    segment.where("filters @> ?", {data: ["20", "40"]})
    The above will return null as there is no 40 in it. If we had ["20", "30"] than we could get one value.
  2. If we want to retrun the value even if only one is there than we can do
    segment.where("(filters -> :key)::jsonb ?| ARRAY [:value]", key: 'data', value: ['20', '40'])
    This will return the value if there is atleast on present in the database.

The structure like {key: 'a', display: 'trial'} is not a good structure to keep as we have to pluck or select each value of a key to another array and query there. This will bring n*2 query which is bad.

So my suggestion after running a performance testing is that whenever we save the data to jsonb object, parse it in such a way that we just need to compare the real query that would come and save it .

Example:
My real data stuct is
values: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}]
Than in your db save it like this.

Table displayers
columns: value:jsonb array: true, filter:jsonb
1 row- value: [{key: 'a', display: 'A'}, {key: 'b', display: 'B'}], filter: { keys: ['a', 'b'] }

Now you can query displayers.filter -> keys with

displayers.where("(filter -> :key)::jsonb ?| ARRAY [:value]", key: 'keys', value: ['a', 'c'])
this will retrun row 1.

I cannot write this in stackoverflow as i m not allowed to write an answer.

Thanks and please let me know if this is not clear.

Thanks!

@mankind

This comment has been minimized.

Copy link
Owner Author

@mankind mankind commented Mar 11, 2021

Thanks @valachi for enriching this gist by your addition.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment