0

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

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

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

Tested in SQLfiddle.

2
  • it is showing count 1 as well, what if i just want all those whom have count more than 1? – Junaid Farooq Dec 18 '15 at 11:33
  • add HAVING COUNT(*) > 1 – ypercubeᵀᴹ Dec 18 '15 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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