Facing Problem in PostgresSQL query for JSON data

I am having following data

{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["123", "456", "789"], "tZone": "PPP"
}

above data store against analytis.col_json column

I am having table structure

CREATE TABLE analytics 
(
    id bigint NOT NULL,
    col_typ character varying(255) COLLATE pg_catalog."default",
    col_json json,
    cre_dte timestamp without time zone,    
    CONSTRAINT clbk_logs_pkey PRIMARY KEY (id)
);

The above records are in n-rows.

I am trying to fetch records on basis of 'refs' by sending list of string. for example:- I have a separate List as a right side values to be filter on my table.

My query is as following

select * FROM public.analytics 
where col_json-> 'refs' in (
'123',
'pqa',
'bhu',
'qwerty'
);

but above query is not working for me.



Read more here: https://stackoverflow.com/questions/64891464/facing-problem-in-postgressql-query-for-json-data

Content Attribution

This content was originally published by Dnyaneshwar Jadhav at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: