How to eliminate newline inside json into one line?-CodePudding

pg12, have this function to extract only text created by Quill Editor:

CREATE OR REPLACE FUNCTION public.fun(
    j json)
    RETURNS text
    LANGUAGE 'plpgsql' immutable
AS $BODY$
begin
  return string_agg((obj->>'insert') || ' ', '')
  FROM json_array_elements(j->'ops') obj;
end;
$BODY$;

select * from fun('{"ops":[{"insert":"1st line\n."},{"insert":"2nd line."},{"insert":"333"}]}') returns

return string_agg(replace((obj->>'insert')::text, '\n', '') || ' ', '') won't do the trick.

How to achieve? 1st line. 2nd line. 333

CodePudding user response：

Use the function replace().

...
begin
  return string_agg(replace(obj->>'insert', e'\n', '') || ' ', '')
...

From the documentation:

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'.