This is table.
CREATE TABLE cameras ( id integer NOT NULL DEFAULT nextval('sq_streams'::regclass), created_at timestamp with time zone NOT NULL DEFAULT now(), updated_at timestamp with time zone NOT NULL DEFAULT now(), exid text NOT NULL, owner_id integer NOT NULL, is_public boolean NOT NULL, config json NOT NULL, name text NOT NULL, last_polled_at timestamp with time zone DEFAULT now(), is_online boolean, timezone text, last_online_at timestamp with time zone DEFAULT now(), location geography(Point,4326), mac_address macaddr, model_id integer, discoverable boolean NOT NULL DEFAULT false, preview bytea, thumbnail_url text, CONSTRAINT pk_streams PRIMARY KEY (id) )
that config object is of type json and containing json data inside it as well.
i have 5000 records in this. What i am trying to get is , There are 57 Cameras and they have everything different but 3 things are same. which are
'external_http_port', config->> 'external_host', config-> 'snapshots'->> 'jpg'
I want to create a query which will show me results as
external_http_port | external_host | config-> 'snapshots'->> 'jpg' | Count "220.127.116.11" | "1024" | "/oneshotimage.jpg" | 57 "18.104.22.168" | "81" |"/onvif/media_service/snapshot"|20