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

On 24.10.2019 18:17, Paul Jungwirth wrote:
> 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,
>

You can simply use jsonb_object_agg() to build a jsonb object from a sequence
of transformed key-value pairs:

SELECT COALESCE(jsonb_object_agg(REPLACE(k, '_', '-'),
jsonb_dasherize(v)), '{}')
INTO ret
FROM jsonb_each(j) AS t(k, v);

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company