ERROR: malformed array literal: "[{...}]" DETAIL: "[" must introduce explicitly--CodePudding

To extract text of insert ({"ops":[{"insert":"just a plain text.\n"},{"insert":"2nd line"}]}) I created this function:

create or replace function _json_extract_text
(
    j json
)
returns text
language 'plpgsql'
as $$
declare vjay json[];
declare txt text := '';
declare i json;
begin
    select (j::json->>'ops')::json into vjay;
    FOREACH i IN ARRAY vjay
    LOOP 
        txt := txt || (select i::json->>'insert') || ' ';
    END LOOP;
    return txt;
end;
$$;

select _json_extract_text('{"ops":[{"insert":"just a plain text.\n"},{"insert":"2nd line"}]}'::json) received:

ERROR: malformed array literal: "[{"insert":"just a plain text.\n"},{"insert":"2nd line"}]"

DETAIL: "[" must introduce explicitly-specified array dimensions.

CONTEXT: PL/pgSQL function current._json_extract_text(json) line 6 at SQL statement SQL state: 22P02

UPDATE: Thanks to @Bergi, it's working as

...
AS $BODY$
begin
  return string_agg(obj->>'insert' || ' ', '')
  FROM json_array_elements(j->'ops') obj;
end;
$BODY$;

CodePudding user response：

json[] is almost always a mistake. You don't have a postgres array of json values. Also you shouldn't use j::json->>'ops' if you don't want to get back text.

To iterate a JSON array is postgres, use json_array_elements.

And instead of an iterative loop in a procedural function, just use the string_agg aggregate function.

CREATE OR REPLACE FUNCTION _json_extract_text(j json) RETURNS text
IMMUTABLE
RETURN (
  SELECT string_agg(obj->>'insert' || ' ', '')
  FROM json_array_elements(j->'ops') obj
);