PostgreSQL - duplicate data show and count 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-&gt;&gt; 'external_host', config-&gt; 'snapshots'-&gt;&gt; 'jpg'


I want to create a query which will show me results as 

external_http_port | external_host | config-&gt; 'snapshots'-&gt;&gt; 'jpg' | Count
"93.87.73.118"     | "1024"        |      "/oneshotimage.jpg"      | 57
"180.180.180.48"   |  "81"         |"/onvif/media_service/snapshot"|20
I think you just want a simple group by query, using the 3 expressions that get data out of the json column:

select 
    config-&gt;&gt;'external_http_port' as external_http_port, 
    config-&gt;&gt;'external_host'      as external_host, 
    config-&gt;'snapshots'-&gt;&gt;'jpg'   as jpg,
    count(*)                      as count
from
    cameras
group by 
    config-&gt;&gt;'external_http_port',
    config-&gt;&gt;'external_host', 
    config-&gt;'snapshots'-&gt;&gt;'jpg' 
-- having                              -- to show only those 
--     count(*) &gt; 1                    -- with count &gt; 1
  ;


Tested in SQLfiddle.