How to eliminate for loop in a nested aggregate problem

Question

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be eliminated and if so how? I am using PostgreSQL version 11.1.

The input (JSON): [{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]

The expected output: {{a="1",b="2"},{c="3",d="4",e=""}}

My plpgsql code:

CREATE OR REPLACE FUNCTION parse(_in JSONB)
RETURNS TEXT LANGUAGE plpgsql STABLE AS $BODY$
DECLARE
    _out TEXT;
    _parts TEXT[];
    _row RECORD;
BEGIN
    FOR _row IN (
        SELECT q1.value, ROW_NUMBER() OVER () AS index
            FROM JSONB_ARRAY_ELEMENTS(_in) q1
    ) LOOP
        _parts[_row.index] := STRING_AGG(CONCAT(q2.key, '=', q2.value::TEXT), ',')
            FROM JSONB_EACH(_row.value) q2;
    END LOOP;

    SELECT CONCAT(
            '{{',
            COALESCE(STRING_AGG(q, '},{'), ''),
            '}}'
        )
        INTO _out
        FROM UNNEST(_parts) q;

    RETURN _out;
END
$BODY$;

Run:

my_db=*# select parse('[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]');
               parse
------------------------------------
 {{a="1",b="2"},{c="3",d="4",e=""}}
(1 row)

UPDATE: In response to request for additional requirements:

The input is an actual json(b) object, not a string representation.
No key or value will ever have a double quote char in it (not even an escaped one.)
The values are always of type string though they might be empty.
There can be a LOT of elements in the JSON.

Erwin Brandstetter · Accepted Answer · 2019-04-19T17:39:44.107

For just the shown example, plain string manipulation would do the job:

WITH tbl(j) AS (SELECT json '[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]')
SELECT '{' || regexp_replace( left(right(j::text, -1), -1)
                            , '"([^"]+)" *:', '\1=', 'g')
    || '}'
FROM  tbl;

If you have more sophisticated keys and values, here is a rewrite of your function. Using a simple SQL function instead:

CREATE OR REPLACE FUNCTION f_parse(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{') || '}}'
FROM   jsonb_array_elements(_in) q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',') AS part
   FROM   jsonb_each(q1.value) q2
   ) q2
$func$;

If you don't trust that the original order of elements is guaranteed, use this safe variant:

CREATE OR REPLACE FUNCTION f_parse_safe(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{' ORDER BY q1.ordinality) || '}}'
FROM   jsonb_array_elements(_in) WITH ORDINALITY q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',' ORDER BY q2.ordinality) AS part
   FROM   jsonb_each(q1.value) WITH ORDINALITY q2
   ) q2
$func$;

See:

How to preserve the original order of elements in an unnested array?

db<>fiddle here

How to eliminate for loop in a nested aggregate problem

1 Answers1