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

Поиск
Список
Период
Сортировка
От Paul Jungwirth
Тема Add json_object(text[], json[])?
Дата
Msg-id 6b8b6d03-7b13-3d02-ca4d-f78a0a026f24@illuminatedcomputing.com
обсуждение исходный текст
Ответы Re: Add json_object(text[], json[])?  (Nikita Glukhov <n.gluhov@postgrespro.ru>)
Re: Add json_object(text[], json[])?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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@illuminatedcomputing.com



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Fabien COELHO
Дата:
Сообщение: Re: pgbench - extend initialization phase control
Следующее
От: Nikita Glukhov
Дата:
Сообщение: Re: Add json_object(text[], json[])?