In PostgreSQL, how to generate clean JSON without the backslashes and outer double quotes?

Question

This PostgreSQL code:

do
$j$
declare arr text[];

begin

arr = array_append(arr, jsonb_build_object('k', 'acg', 'v', 'val')::text);
arr = array_append(arr, jsonb_build_object('k', 'xyz', 'v', 'xxx')::text);

raise info '%', (array_to_json(arr));
end;
$j$

Generates this:

["{\"k\": \"acg\", \"v\": \"val\"}","{\"k\": \"xyz\", \"v\": \"xxx\"}"]

I know I can run regexp_replace and clean it up, but isn't there a way to generate clean JSON?

Like this?:

[{"k": "acg", "v": "val"},{"k": "xyz", "v": "xxx"}]

Answer 1

I think that you didn't intend to use text here, but want jsonb.

The code then would look as follows:

DO                                                                          
$j$                                                                         
DECLARE                                                                     
   arr jsonb[];                                                             
BEGIN                                                                       
   arr = array_append(arr, jsonb_build_object('k', 'acg', 'v', 'val'));     
   arr = array_append(arr, jsonb_build_object('k', 'xyz', 'v', 'xxx'));     

   RAISE NOTICE '%', to_jsonb(arr);                                         
END;                                                                        
$j$;

Te result is:

NOTICE:  [{"k": "acg", "v": "val"}, {"k": "xyz", "v": "xxx"}]

Answer 2

The extra characters you see are not part of the value, only of the display representation.

Generates this:

["{\"k\": \"acg\", \"v\": \"val\"}","{\"k\": \"xyz\", \"v\": \"xxx\"}"]

That is (the output representation of) an array with two items; each item is a JSON string. That's what your code requested:

arr = array_append(arr, jsonb_build_object('k', 'acg', 'v', 'val')::text);
arr = array_append(arr, jsonb_build_object('k', 'xyz', 'v', 'xxx')::text);

If you are going to use the JSON, you definitely want it as the text string you're getting.

The representation of that string will, of course, need to have special characters escaped to be clear which characters are part of the string, and which are not.

A text string value whose value is {"k": "acg", "v": "val"} will be represented for display with some of those characters escaped with backslashes, "{\"k\": \"acg\", \"v\": \"val\"}". The backslashes and surrounding quotes and other extra characters are not part of the value.

So you need to choose between rendering the value for display as a PostgreSQL literal value, which requires escaping its special characters for output; or using the text string elsewhere (pass the value to another function, for example) as an actual value.