Postgres function with jsonb parameters

Question

I have seen a similar post here but my situation is slightly different from anything I've found so far. I am trying to call a postgres function with parameters that I can leverage in the function logic as they pertain to the jsonb query. Here is an example of the query I'm trying to recreate with parameters.

SELECT * 
  from edit_data 
  where ( "json_field"#>'{Attributes}' )::jsonb @> 
                    '{"issue_description":"**my description**",  
                      "reporter_email":"**user@generic.com**"}'::jsonb

I can run this query just fine in PGAdmin but all my attempts thus far to run this inside a function with parameters for "my description" and "user@generic.com" values have failed. Here is a simple example of the function I'm trying to create:

CREATE OR REPLACE FUNCTION get_Features(
    p1 character varying,
    p2 character varying)
  RETURNS SETOF edit_metadata AS
$BODY$ 
SELECT * from edit_metadata where ("geo_json"#>'{Attributes}' )::jsonb @> '{"issue_description":**$p1**, "reporter_email":**$p2**}'::jsonb;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

I know that the syntax is incorrect and I've been struggling with this for a day or two. Can anyone help me understand how to best deal with these double quotes around the value and leverage a parameter here?

TIA

Answer 1

You could use function json_build_object:

select json_build_object(
    'issue_description', '**my description**',
    'reporter_email', '**user@generic.com**');

And you get:

                                    json_build_object                                    
-----------------------------------------------------------------------------------------
 {"issue_description" : "**my description**", "reporter_email" : "**user@generic.com**"}
(1 row)

That way there's no way you will input invalid syntax (no hassle with quoting strings) and you can swap the values with parameters.