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":"**[email protected]**"}'::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 "[email protected]" 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

It is a String, just concatenate it like '{"issue_description":"'||yourParam||'" and so on. — Jorge Campos
– Jorge Campos, Commented Mar 2, 2016 at 18:00
I have also tried something along these lines: CREATE OR REPLACE FUNCTION get_customentitywithserializeddatatest7( p1 character varying, p2 character varying) RETURNS SETOF edit_metadata AS $BODY$ SELECT * from edit_metadata where ( "geo_json"#>'{Attributes}' )::jsonb @> '{"issue_description":COALESCE($1, issue_description), "reporter_email":COALESCE($2, reporter_email)}'::jsonb; $BODY$ LANGUAGE sql VOLATILE COST 100 ROWS 1000; — DenverDevGuy
– DenverDevGuy, Commented Mar 2, 2016 at 18:09
You still don't get it, this '{"issue_description":"**my description**", "reporter_email":"**[email protected]**"}' is a String and as so you cant use funcion, command or variables inside it and hope postgresql will understand, it is not PHP, you have to say to it that you are forming a NEW string. It should be like (for your case): '{"issue_description":"'||p1||'", "reporter_email":"'||p2||'"}' That way you are telling to postgresql that you want to "inject" your parameter inside your String — Jorge Campos
– Jorge Campos, Commented Mar 2, 2016 at 18:14
What post are you talking about? Another question or another comment? — Jorge Campos
– Jorge Campos, Commented Mar 2, 2016 at 18:26

hruske · Accepted Answer · 2016-03-02 19:09:59Z

1

You could use function json_build_object:

select json_build_object(
    'issue_description', '**my description**',
    'reporter_email', '**[email protected]**');

And you get:

                                    json_build_object                                    
-----------------------------------------------------------------------------------------
 {"issue_description" : "**my description**", "reporter_email" : "**[email protected]**"}
(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.

answered Mar 2, 2016 at 19:09

hruske

2,24319 silver badges27 bronze badges

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

2 Comments

DenverDevGuy Over a year ago

Thanks for your response hruske, I'm looking into this but still unclear how to implement it with my current scenario as I'm very new to Postgres and jsonb in particular.

DenverDevGuy Over a year ago

json_build_object turned the key, thank you very much!

Collectives™ on Stack Overflow

Postgres function with jsonb parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related