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[])?
Re: Add json_object(text[], json[])? |
Список | 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 по дате отправления: