Inserting JSON data into Postgresql from Jenkins pipeline

Question

I'm trying to insert JSON data into my database from Jenkins pipeline, unfortunately I have problem with proper format. My query looks like that:

def insertQuery = """
                  INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"key": "value"}');
                  """

and later:

  sh """
     PGPASSWORD="${dbPassword}" psql -h ${dbHost} -p ${dbPort} -d ${dbName} -U ${dbUser} -c "${insertQuery}" -w
     """

Error I'm getting:

ERROR:  invalid input syntax for type json
LINE 2: ... INTO public.metrics(pr, info) VALUES ('KP-1111', '{key: val...
                                                             ^
DETAIL:  Token "key" is invalid.
CONTEXT:  JSON data, line 1: {key...

I tried to escape signs, move JSON into variable instead of hardcode in query. Is there any other option I can insert this data?

Answer 1

You have quietly mismatched quotes. The query string has key and value surrounded in double quotes, then the sh again surrounds the entire query in double quotes, so your -c "${insertQuery}" ends up evaluated to:

-c "INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"key": "value"}');"

which terminates the string at the double quote before key, concatenates it with key, reopens quoted string and continues up until value and does the same, cutting it up like this:

"INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{"
key
": "
value
"}');"

So you end up losing those double quotes entirely. Which is why you can see the error message without them around key and value:

"INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{key: value}');"

You can escape the quotes with \ to avoid them terminating the string in your sh:

def insertQuery = """
                  INSERT INTO public.metrics(pr, info) VALUES ('KP-1111', '{\\"key\\": \\"value\\"}');
                  """