Обсуждение: 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, -- Paul ~{:-) pj@illuminatedcomputing.com
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
Paul Jungwirth <pj@illuminatedcomputing.com> writes: > 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 think a potential problem is creation of ambiguity where there was none before. I prototyped this as regression=# create function jsonb_object(text[], jsonb[]) returns jsonb as 'select jsonb_object($1, $2::text[])' language sql; CREATE FUNCTION and immediately got regression=# explain select jsonb_object('{a}', '{b}'); ERROR: function jsonb_object(unknown, unknown) is not unique LINE 1: explain select jsonb_object('{a}', '{b}'); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. which is something that works fine as long as there's only one jsonb_object(). I'm not sure whether that's a big problem in practice --- it seems like it will resolve successfully as long as at least one input isn't an unknown literal. But it could be a problem for prepared statements, or clients using APIs that involve prepared statements under the hood: regression=# prepare foo as select jsonb_object($1,$2); ERROR: function jsonb_object(unknown, unknown) is not unique Also, as the prototype implementation shows, it's not like you can't get this functionality today ... you just need to cast jsonb to text. Admittedly that's annoying and wasteful. regards, tom lane
On Thu, Oct 24, 2019 at 8:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think a potential problem is creation of ambiguity where there was > none before. I agree that's not nice, and it seems like a new name might be better. > Also, as the prototype implementation shows, it's not like you > can't get this functionality today ... you just need to cast > jsonb to text. Admittedly that's annoying and wasteful. I don't think that gives the same result, does it? For example: # select jsonb_object(array['foo'], array['[{"bar-bar": ["baz"]}]'::jsonb]); jsonb_object --------------------------------------- {"foo": "[{\"bar-bar\": [\"baz\"]}]"} You can see the values are JSON strings, not JSON arrays/objects/etc. Regards, Paul
On Thu, Oct 24, 2019 at 8:45 AM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > You can simply use jsonb_object_agg() to build a jsonb object from a sequence > of transformed key-value pairs: <strikes forehead> I've even used that function before. :-) I tried finding it on the JSON functions page but couldn't, so I thought maybe I was going crazy. Of course it's on the aggregates page instead. As I said it was late at night. :-) Your version works perfectly! Even still, it may be nice to have a non-aggregate function that lets you build nested JSON. But I agree jsonb_object_agg makes it less needful. Thanks! Paul
Paul A Jungwirth <pj@illuminatedcomputing.com> writes: > On Thu, Oct 24, 2019 at 8:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Also, as the prototype implementation shows, it's not like you >> can't get this functionality today ... you just need to cast >> jsonb to text. Admittedly that's annoying and wasteful. > I don't think that gives the same result, does it? Ah, you're right --- ENOCAFFEINE :-(. regards, tom lane
On 10/24/19 12:46 PM, Paul A Jungwirth wrote: > > Even still, it may be nice to have a non-aggregate function that lets > you build nested JSON. But I agree jsonb_object_agg makes it less > needful. > json{b}_build_object and json{b}_build_array are designed for creating nested json{b}. Not sure if they would work for your purpose. I hadn't considered something to let you transform keys. PLV8 is useful for doing more outlandish JSON transformations. Maybe the Underscore library has something that would be useful here. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 10/25/19 6:40 AM, Andrew Dunstan wrote: > json{b}_build_object and json{b}_build_array are designed for creating > nested json{b}. Not sure if they would work for your purpose. Thanks for the suggestion! I looked at these a bit, but they only work if you have a known-ahead-of-time number of arguments. (I did explore building an array and calling jsonb_build_object using VARIADIC, but you can't build an array with alternating text & jsonb elements. That made me curious how these functions even worked, which led me to extract_variadic_args (utils/fmgr/funcapi.c), which has some magic to support heterogeneous types when not called with the VARIADIC keyword, so it seems they bypass the normal variadic handling.) Regards, -- Paul ~{:-) pj@illuminatedcomputing.com