Skip to content

Instantly share code, notes, and snippets.

@mankind
Last active January 29, 2024 05:38
Star You must be signed in to star a gist
Save mankind/1802dbb64fc24be33d434d593afd6221 to your computer and use it in GitHub Desktop.
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
Copy link

Thanks for this! Saved me evening...

@neymarsabin
Copy link

Awesome. 👍

@bramj
Copy link

bramj commented Jul 5, 2018

Great stuff, thanks!

@skplunkerin
Copy link

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
Copy link

Thanks for the note.

@NirAviel
Copy link

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
Copy link

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
Copy link
Author

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
Copy link

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
Copy link

GesJeremie commented Jan 5, 2020

Thanks! You saved me a lot of time :)

@silverdr
Copy link

silverdr commented Nov 1, 2020

FWIW - nested structure query examples would also be helpful

@valachi
Copy link

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
Copy link
Author

mankind commented Mar 11, 2021

Thanks @valachi for enriching this gist by your addition.

@Dimaon
Copy link

Dimaon commented Jul 13, 2021

Hi! How i can unscoped jsonb conditions?

@asad-shakil
Copy link

My table order has a jsonb column called line_items that can contain 1 or more line items like this:

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  },
  {
    "id":9994857578581,
    "sku":"NATURAL-SHADOW_C1_24H",
  }
]

I need to find the count of all orders that have sku = CLIPPING-PATH_C2_24H && there is no second line item in it unlike the above one. So, the query won't count the above one but the one below:

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  }
]

Could you advise how to achieve this?

@asad-shakil
Copy link

I was able to figure this out using the jsonb_array_length method:

Order
.where("line_items @> ?", [{sku: sku}].to_json)
.where("jsonb_array_length(line_items) = 1")
.count
end

@netuoso
Copy link

netuoso commented May 26, 2022

This is extremely useful. Thank you so much for sharing.

@gamesover
Copy link

Is there a way to search json field globally?

I don't know what's json data structure and I don't care at all.

I want to search all keys and all values inside the json field. Or, is there a way to flatten the json become a string and search the string?

@slhck
Copy link

slhck commented Oct 18, 2022

For JSON arrays you can use json_array_elements_text to convert to text and search that. Otherwise I think that jsonb_pretty might work, as that convers the JSON content to text, which you can search.

@JayeshB92
Copy link

JayeshB92 commented Dec 16, 2022

I have structure like project_data:jsonb field in postgres table
project_data: [{"data"=>"1", "template_field_id"=>1}, {"data"=>"10-11-2022", "template_field_id"=>2}, {"data"=>"Hi", "template_field_id"=>3}, {"data"=>"abc", "template_field_id"=>8}, {"data"=>"2", "template_field_id"=>9}]

data field will always have string values.
template_field_id will always have integer values (i.e. id of another table).

I want to get list of all records based on data field and template_field_id field. Below criteria only applies to data field values.

  1. Filter by date range (i.e. between 2 dates)
  2. Filter having exact text
  3. Filter having number between range, including number (i.e. data field having number between 3 to 10)
  4. Filter matching partial value.
  5. How to group (Grouping Query)

Could you advise how to achieve this with single and multiple filters?

@vladalive
Copy link

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