Add json_object(text[], json[])?

From: Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add json_object(text[], json[])?
Date: 2019-10-24 15:17:23
Message-ID: 6b8b6d03-7b13-3d02-ca4d-f78a0a026f24@illuminatedcomputing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I noticed that our existing 2-param json{,b}_object functions take
text[] for both keys and values, so they are only able to build
one-layer-deep JSON objects. I'm interested in adding json{,b}_object
functions that take text[] for the keys and json{,b}[] for the values.
It would otherwise behave the same as json_object(text[], text[]) (e.g.
re NULL handling). Does that seem worthwhile to anyone?

I'll share my specific problem where I felt I could use this function,
although you can stop reading here if that isn't interesting to you. :-)
I was building a jsonb_dasherize(j jsonb) function, which converts
snake_case JSON keys into dashed-case JSON keys. (It's because of a
Javascript framework.... :-) My function needs to walk the whole JSON
structure, doing this recursively when it sees objects inside arrays or
other objects. Here is the definition, including a comment where my
proposed jsonb_object would have helped:

CREATE FUNCTION jsonb_dasherize(j jsonb)
RETURNS jsonb
IMMUTABLE
AS
$$
DECLARE
t text;
key text;
val jsonb;
ret jsonb;
BEGIN
t := jsonb_typeof(j);
IF t = 'object' THEN
-- So close! If only jsonb_object took text[] and jsonb[] params....
-- SELECT jsonb_object(
-- array_agg(dasherize_key(k)),
-- array_agg(jsonb_dasherize(v)))
-- FROM jsonb_each(j) AS t(k, v);
ret := '{}';
FOR key, val IN SELECT * FROM jsonb_each(j) LOOP
ret := jsonb_set(ret,
array[REPLACE(key, '_', '-')],
jsonb_dasherize(val), true);
END LOOP;
RETURN ret;
ELSIF t = 'array' THEN
SELECT COALESCE(jsonb_agg(jsonb_dasherize(elem)), '[]')
INTO ret
FROM jsonb_array_elements(j) AS t(elem);
RETURN ret;
ELSIF t IS NULL THEN
-- This should never happen internally
-- but only from a passed-in NULL.
RETURN NULL;
ELSE
-- string/number/null:
RETURN j;
END IF;
END;
$$
LANGUAGE plpgsql;

I also tried a recursive CTE there using jsonb_set, but it was too late
at night for me to figure that one out. :-)

It seems like a json-taking json_object would be just what I needed. And
in general I was surprised that Postgres didn't have a more convenient
way to build multi-layer JSON. I'm happy to add this myself if other
folks want it.

Regards,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nikita Glukhov 2019-10-24 15:42:26 Re: Add json_object(text[], json[])?
Previous Message Fabien COELHO 2019-10-24 15:06:16 Re: pgbench - extend initialization phase control