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

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