Postgres JSON数据类型Rails查询 [英] Postgres JSON data type Rails query

问题描述

我正在使用Postgres的json数据类型，但想对嵌套在json中的数据进行查询/排序.

I am using Postgres' json data type but want to do a query/ordering with data that is nested within the json.

我想在json数据类型上使用.where进行排序或查询.例如，我要查询关注者人数> 500的用户，或者要按关注者或关注人数排序.

I want to order or query with .where on the json data type. For example, I want to query for users that have a follower count > 500 or I want to order by follower or following count.

谢谢！

示例:

model User

data: {
     "photos"=>[
       {"type"=>"facebook", "type_id"=>"facebook", "type_name"=>"Facebook", "url"=>"facebook.com"}
      ], 
     "social_profiles"=>[
         {"type"=>"vimeo", "type_id"=>"vimeo", "type_name"=>"Vimeo", "url"=>"http://vimeo.com/", "username"=>"v", "id"=>"1"},
         {"bio"=>"I am not a person, but a series of plants", "followers"=>1500, "following"=>240, "type"=>"twitter", "type_id"=>"twitter", "type_name"=>"Twitter", "url"=>"http://www.twitter.com/", "username"=>"123", "id"=>"123"}
     ]
}

推荐答案

对于任何偶然发现此问题的人.我想出了一个使用ActiveRecord和Postgres的JSON数据类型的查询列表.随时对其进行编辑以使其更加清晰.

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.

Postgres重要Rails命令:

# Sort based on the Hstore data:
2.1.1 :022 > Post.order("data->'hello' DESC")

=> #<ActiveRecord::Relation [#<Post id: 4, created_at: "2014-04-16 01:05:49", updated_at: "2014-04-16 01:05:49", data: {"hi"=>"23", "hello"=>"22"}>, #<Post id: 3, created_at: "2014-04-16 01:05:37", updated_at: "2014-04-16 01:05:37", data: {"hi"=>"13", "hello"=>"21"}>, #<Post id: 2, created_at: "2014-04-16 01:05:28", updated_at: "2014-04-16 01:05:28", data: {"hi"=>"3", "hello"=>"2"}>, #<Post id: 1, created_at: "2014-04-16 01:05:05", updated_at: "2014-04-16 01:05:05", data: {"hi"=>"2", "hello"=>"1"}>]> 



# Where inside a JSON object:
Record.where("data ->> 'likelihood' = '0.89'")

# Searching nested json object:
2.1.1 :130 > r.column_data
 => {"data1"=>[1, 2, 3], "data2"=>"data2-3", "array"=>[{"hello"=>1}, {"hi"=>2}], "nest"=>{"nest1"=>"yes"}} 

2.1.1 :130 > Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ")

# Searching within array:
Record.where("column_data #>> '{data1,1}' = '2' ")


# Searching 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%' ")
# This is advised against in rails, use below:
Record.where("column_data ->> 'array' LIKE ?", "%hello%")

更新 我将在不久的将来写一篇博客文章，并将链接放在这里.

Update I will be writing a blog post on this very shortly and will put the link here.

这篇关于Postgres JSON数据类型Rails查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！