Last active
January 6, 2023 14:15
-
-
Save guilhermegazzinelli/6c66f6f460dd3a37b7b8db44c2f202c3 to your computer and use it in GitHub Desktop.
JSON Postgres query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Sort based on the Hstore data: | |
Post.order("data->'hello' DESC") | |
=> #<ActiveRecord::Relation [ | |
#<Post id: 4, data: {"hi"=>"23", "hello"=>"22"}>, | |
#<Post id: 3, data: {"hi"=>"13", "hello"=>"21"}>, | |
#<Post id: 2, data: {"hi"=>"3", "hello"=>"2"}>, | |
#<Post id: 1, data: {"hi"=>"2", "hello"=>"1"}>]> | |
# Where inside a JSON object: | |
Record.where("data ->> 'likelihood' = '0.89'") | |
# Example json object: | |
r.column_data | |
=> {"data1"=>[1, 2, 3], | |
"data2"=>"data2-3", | |
"array"=>[{"hello"=>1}, {"hi"=>2}], | |
"nest"=>{"nest1"=>"yes"}} | |
# Nested search: | |
Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ") | |
# Search within array: | |
Record.where("column_data #>> '{data1,1}' = '2' ") | |
# Search within a value that's an array: | |
Record.where("column_data #> '{array,0}' ->> 'hello' = '1' ") | |
# this only find for one element of the array. | |
# All elements: | |
Record.where("column_data ->> 'array' LIKE '%hello%' ") # bad | |
Record.where("column_data ->> 'array' LIKE ?", "%hello%") # good |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Referencia Postgres: https://www.postgresql.org/docs/current/functions-json.html
https://stackoverflow.com/a/24463973