How to extract the values from json input in postgresql?

Naveenkumar K

I am working with the PostgreSQL stored function, I need to extract the values from the JSON input and match with the column in the table and return the table in JSON format. My JSON input,

{
"sensor":"sensor1",
"fromdate":date,
"todate":"date
}

my sensortable here is the function I worked so far

select array_to_json(array_agg(row_to_json(d)))
      from (
        select sensor,id,value,created_date
        from probe_data
        where  probe_data.sensor =sensors
         AND probe_data.created_date >=fromdate AND  probe_data.created_date <= todate
      ) d
select x.sensores,x.fromdate,x.todate from json_to_recordset($1) x
(
sensors text,
fromdate timestamp,
todate timestamp)

As you can see, I can able to get the data from JSON input but I don't know how to access it inside the WHERE condition. I need some help to do this.

Akhilesh Mishra

simply you can use JSONB operators to achieve this like below:

Try this:

create or replace function example (param1 jsonb) 
returns table (sensor_ varchar, id_ int, value_ numeric, created_date_ TIMESTAMP) 
as 
$body$
    begin
    RETURN query
    select sensor, id, value, created_date from probe_data 
    where 
    created_date >=(param1->>'fromdate')::TIMESTAMP 
    and 
    created_date <= (param1->>'todate')::TIMESTAMP;

    end;

$body$ 

LANGUAGE plpgsql;

if you want the above output in JSON ARRAY format then

Try This:

create or replace function example1 (param1 jsonb) 
returns jsonb 
as 
$body$
declare 
    jsondata jsonb;

begin
    
    jsondata = (select 
            array_to_json(array_agg(row_to_json(d))) from (
            select sensor, id, value, created_date from probe_data 
            where 
            created_date >=(param1->>'fromdate')::TIMESTAMP 
            and 
            created_date <= (param1->>'todate')::TIMESTAMP ) d);
RETURN jsondata;
end;
$body$
LANGUAGE plpgsql;

FIDDLE

Collected from the Internet

Please contact [email protected] to delete if infringement.