0

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.
  • Depends on the possible range of input values. One example value doesn't define much. – Erwin Brandstetter Apr 19 at 16:24
1

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:

db<>fiddle here

  • If your requirements are more sophisticated, please clarify your question. – Erwin Brandstetter Apr 19 at 16:45
  • Thanks f_parse() is exactly what I needed - as to the requirements, that is about it except a LOT more items in the json (and order of the elements does not matter in my case.) – user9645 Apr 24 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.