This is table.

  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
""     | "1024"        |      "/oneshotimage.jpg"      | 57
""   |  "81"         |"/onvif/media_service/snapshot"|20

1 Answer 1


I think you just want a simple group by query, using the 3 expressions that get data out of the json column:

    config->>'external_http_port' as external_http_port, 
    config->>'external_host'      as external_host, 
    config->'snapshots'->>'jpg'   as jpg,
    count(*)                      as count
group by 
-- having                              -- to show only those 
--     count(*) > 1                    -- with count > 1

Tested in SQLfiddle.

  • it is showing count 1 as well, what if i just want all those whom have count more than 1? Dec 18, 2015 at 11:33
  • add HAVING COUNT(*) > 1 Dec 18, 2015 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.