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
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 |