Duplicate found queries on Json data - postgress

Question

am having issues in running queries on data which i have been provided, if i do Camera.first get the results as

#<Camera id: 6, created_at: "2013-12-12 17:30:32", updated_at: "2015-11-19 10:19:33", exid: "dublin-rememberance-floor2", owner_id: 4, is_public: true, config: {"snapshots"=>{"jpg"=>"/onvif/snapshot"}, "external_http_port"=>8105, "external_host"=>"89.101.225.158", "auth"=>{"basic"=>{"username"=>"admin", "password"=>"mehcam"}}}, name: "My Camera", last_polled_at: "2015-11-27 15:01:51", is_online: false, timezone: nil, last_online_at: "2014-05-01 09:45:39", location: "0101000020E6100000875341A08E0A19C0D6BB896BC6AC4A40", mac_address: nil, model_id: 6, discoverable: false, preview: nil, thumbnail_url: nil>

I am totally unaware about how to get the query run CONFIG object. example: I want to select all the Cameras list which have same

"external_http_port"=>8105, "external_host"=>"89.101.225.158"

but they are in Config Object.

What kind of type a `config` column `hstore`, `json`, `jsonb`? — Roman Kiselenko, Dec 16 '15 at 12:19
config is an object of json type. All data is in postgres and config is an attribute but type is json — Junaid Farooq, Dec 16 '15 at 12:22
here you go https://github.com/evercam/evercam-dashboard/blob/master/db/schema.rb — Junaid Farooq, Dec 16 '15 at 12:29
if you want to see that what is config https://gist.github.com/ijunaidfarooq/783c5d8f959ee8fb2b2c — Junaid Farooq, Dec 16 '15 at 12:32
This may help you http://stackoverflow.com/questions/22667401/postgres-json-data-type-rails-query and if you want to deals with multiple levels: http://stackoverflow.com/questions/23609331/how-do-i-search-over-a-json-type-of-postgres-in-multilevels-using-active-record — Gagan Gami, Dec 16 '15 at 12:45

score 0 · Answer 1 · answered Dec 16 '15 at 12:21

0

You can write query like following for json data fetch:

Camere.where("config->> 'external_http_port' = ? and config->> 'external_host' = ?", 8105, '9.101.225.158')

answered Dec 16 '15 at 12:21

Triveni Badgujar

941
4
8

thanks for your reply, query is awesome, but what i want to get is all those entries which have same external_http_port and host, suppose only external_host. but how to query this? that all those entries which have more than one same external host? name : abc external_host:34:456:09 name : loop external_host:34:46:09 name : acd external_host:34:456:09 now results should be name : abc external_host:34:456:09 and name : acd external_host:34:456:09 – Junaid Farooq Dec 16 '15 at 13:19

Duplicate found queries on Json data - postgress

1 Answers1