Обсуждение: 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

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

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