Extracting key from json string having null value

Question

I have following function

CREATE OR REPLACE FUNCTION public."test"(
    _data text)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
    
    declare _sdate date:= null;
    
    begin
        
        select json_extract_path(_data:: json , 'sdate') into _sdate;
        select _data:: json -> 'sdate' into _sdate;
    END;
$BODY$;

I tried both way but it throws syntax error when sdate is null

I am calling like

select public."test"($${
        "sdate":null
      }$$)

but when I give sdate value it is working

select public."test"($${
        "sdate":"2020-1-01"
      }$$)

I also tried explicity parsing like

select _data:: json -> 'sdate'::date into _sdate;

But not working

user330315 · Accepted Answer · 2020-11-04 07:01:21Z

1

You have many errors in your function. The first one being that you are not returning anything as you are missing a return statement.

Your expression: _data:: json -> 'sdate'::date is not working because the cast operator :: binds more strongly than the ->' operator and thus you are casting 'sdate'to a date, not the result of the->` operator.

You should also declare the function parameter as json, rather than casting it inside the function. And you need to use ->> to return the value as a text value because there is no direct cast from json to date.

You are casting the result to a date, but your function is declared to return integer which also doesn't match.

CREATE OR REPLACE FUNCTION public."test"(_data json)
  RETURNS date --<< here
  LANGUAGE plpgsql
  stable
AS 
$BODY$
declare 
  _sdate date := null;
begin
  _sdate := (_data ->> 'sdate')::date;
  return _sdate;
END;
$BODY$;

The way you formatted your code suggests that you think declare is needed for each variable. But declare starts a block that can contain multiple declarations. declarations, it's not something that needs to be repeated for each variable.

answered Nov 4, 2020 at 7:01

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Md. Parvez Alam Over a year ago

I have given the only part that is not working function... might be some typo while preparing, but the actual problem i am facing is when i pass null in date key in json it throws error but when I pass the value it works

Md. Parvez Alam Over a year ago

I am getting error invalid input syntax for type date: "null", and this exactly here "select _data:: json -> 'sdate' into _sdate"

user330315 Over a year ago

Well, if you only show part of the problem, how do you expect to get the answer to your real problem? My function works without problems with a NULL input: dbfiddle.uk/…

Md. Parvez Alam Over a year ago

Thanks with the help of your fiddle I found my function working.... just curious to know if you could help... the diff was I was using -> rather than ->> and ::date casting at the end not working with ->

user330315 Over a year ago

I have explained that in the second and third paragraph of my answer

Collectives™ on Stack Overflow

Extracting key from json string having null value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related