-
-
Save mankind/1802dbb64fc24be33d434d593afd6221 to your computer and use it in GitHub Desktop.
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") | |
``` | |
Awesome. 👍
Great stuff, thanks!
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")
Thanks for the note.
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... :(
#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?
@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.
@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
-
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. -
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! You saved me a lot of time :)
FWIW - nested structure query examples would also be helpful
@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
- 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.- 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!
Thanks @valachi for enriching this gist by your addition.
Hi! How i can unscoped jsonb conditions?
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?
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
This is extremely useful. Thank you so much for sharing.
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?
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.
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.
- Filter by date range (i.e. between 2 dates)
- Filter having exact text
- Filter having number between range, including number (i.e. data field having number between 3 to 10)
- Filter matching partial value.
- How to group (Grouping Query)
Could you advise how to achieve this with single and multiple filters?
Thanks for this! Saved me evening...