Postgres JSON data type Rails query

by

For any who stumbles upon this. I have come up with a list of queries using ActiveRecord and Postgres’ JSON data type. Feel free to edit this to make it more clear.

Documentation to the JSON operators used below: https://www.postgresql.org/docs/current/functions-json.html.

# 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

More Related Contents:

  1. How do I modify fields inside the new PostgreSQL JSON datatype?
  2. Flatten aggregated key/value pairs from a JSONB field?
  3. Explanation of JSONB introduced by PostgreSQL
  4. Postgres: How to convert a json string to text?
  5. How to query a json column for empty objects?
  6. PostgreSQL return result set as JSON array?
  7. How to create index on JSON field in Postgres?
  8. How to convert postgres json to integer
  9. Check if a Postgres JSON array contains a string
  10. Merging JSONB values in PostgreSQL?
  11. Collect Recursive JSON Keys In Postgres
  12. Size limit of JSON data type in PostgreSQL
  13. In postgresql, how can I return a boolean value instead of string on a jsonb key?
  14. Go JSON Naming strategy about extends
  15. How do I initialize a TypeScript Object with a JSON-Object?
  16. How to escape double quotes in JSON
  17. Using codable with value that is sometimes an Int and other times a String
  18. How to parse/deserialize dynamic JSON
  19. Run batch file with psql command without password
  20. Elasticsearch Bulk Index JSON Data
  21. Add JSON serializer to every model class?
  22. How do I send a JSON string in a POST request in Go
  23. Can JSON numbers be quoted?
  24. jq: print key and value for each entry in an object
  25. How do I `jsonify` a list in Flask? [duplicate]
  26. Mapping JSON data in JQGrid
  27. Decoding Error — Expected to decode Dictionary but found an array instead
  28. Can I encrypt my JSON data?
  29. “Fatal error: ‘EXTERN.h’ file not found” while installing Perl modules
  30. How to read local json import in flutter?

Leave a Comment