Thread: proposal: jsonb_populate_array

proposal: jsonb_populate_array

From
Pavel Stehule
Date:
Hi

Now, there is no native functionality for conversion from json(b) value to some array.


It should not be too hard to implement native function jsonb_populate_array

jsonb_populate_array(anyarray, jsonb) returns anyarray

Usage:

select jsonb_populate_array(null::text[], '["cust_full_name","cust_email"]')

Comments, notes?

Regards

Pavel


Re: proposal: jsonb_populate_array

From
Alvaro Herrera
Date:
On 2023-Aug-14, Pavel Stehule wrote:

> jsonb_populate_array(anyarray, jsonb) returns anyarray
> 
> Usage:
> 
> select jsonb_populate_array(null::text[], '["cust_full_name","cust_email"]')

I don't understand what this does.  Can you be more explicit?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)



Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


po 14. 8. 2023 v 11:32 odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org> napsal:
On 2023-Aug-14, Pavel Stehule wrote:

> jsonb_populate_array(anyarray, jsonb) returns anyarray
>
> Usage:
>
> select jsonb_populate_array(null::text[], '["cust_full_name","cust_email"]')

I don't understand what this does.  Can you be more explicit?

example

'["2023-07-13","2023-07-14"]'::jsonb -->  {2023-07-13,2023-07-14}::date[]

Now, I have to transform to table, casting, and back transformation to array, and I cannot to write generic function. I can run just "slow" query

select array_agg(value::date) from jsonb_array_elements_text('["2023-07-13","2023-07-14"]'::jsonb);

with proposed function I can write

select jsonb_populate_array(null:date[], '["2023-07-13","2023-07-14"]'::jsonb)

Regards

Pavel



--
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)

Re: proposal: jsonb_populate_array

From
Erik Rijkers
Date:
Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32 
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>
 > with proposed function I can write
 >
 > select jsonb_populate_array(null:date[],
 > '["2023-07-13","2023-07-14"]'::jsonb)
 >
Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
     '["2023-07-13", "2023-07-14"]'::jsonb
   , '$' returning date[]
);
        json_query
-------------------------
  {2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Let's hope it gets submitted 17-ish, anyway

Erik








Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>
 > with proposed function I can write
 >
 > select jsonb_populate_array(null:date[],
 > '["2023-07-13","2023-07-14"]'::jsonb)
 >
Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
     '["2023-07-13", "2023-07-14"]'::jsonb
   , '$' returning date[]
);
        json_query
-------------------------
  {2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Yes, the functionality is exactly the same, but still maybe for  completeness the function json_populate_array can be nice.







Let's hope it gets submitted 17-ish, anyway

Erik





Re: proposal: jsonb_populate_array

From
Chapman Flack
Date:
On 2023-08-14 09:11, Erik Rijkers wrote:
>   , '$' returning date[]

I certainly like that syntax better.

It's not that the "here's a null to tell you the type I want"
is terribly unclear, but it seems not to be an idiom I have
seen a lot of in PostgreSQL before now. Are there other places
it's currently used that I've overlooked?

Regards,
-Chap



Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


po 14. 8. 2023 v 15:47 odesílatel Chapman Flack <chap@anastigmatix.net> napsal:
On 2023-08-14 09:11, Erik Rijkers wrote:
>   , '$' returning date[]

I certainly like that syntax better.

It's not that the "here's a null to tell you the type I want"
is terribly unclear, but it seems not to be an idiom I have
seen a lot of in PostgreSQL before now. Are there other places
it's currently used that I've overlooked?

It is used only for hstore, json, jsonb function if I remember correctly.

I dislike this idiom too, but SQL cannot use type as parameter. I proposed anytype polymorphic pseudotype so instead

fx(null::int, ...) you can write (theoretically) fx('int', ...), but it doesn't look too much better. For composite functions we can dynamically to specify structure as SELECT FROM fx(...) AS (a int, b int), but it cannot be used for scalar functions and cannot be used for static composite types.

I can imagine some special syntax of CAST, that can push type to inside function, and allows to us to write functions like fx(non polymorphic types) RETURNS any

for proposed functionality it can look like SELECT CAST(json_populate_array('[]'::jsonb) AS date[])




Regards,
-Chap

Re: proposal: jsonb_populate_array

From
jian he
Date:
\df jsonb_populate_record
                                 List of functions
   Schema   |         Name          | Result data type | Argument data
types | Type
------------+-----------------------+------------------+---------------------+------
 pg_catalog | jsonb_populate_record | anyelement       | anyelement,
jsonb   | func
(1 row)

manual:
> anyelement  Indicates that a function accepts any data type.
> For the “simple” family of polymorphic types, the matching and deduction rules work like this:
> Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data
type,but in any given call they must all be the same actual type. 

So jsonb_populate_record signature can handle cases like
jsonb_populate_record(anyarray, jsonb)? obviously this is a cast, it
may fail.
also if input is anyarray, so the output anyarray will have the same
base type as input anyarray.



Re: proposal: jsonb_populate_array

From
Vik Fearing
Date:
On 8/14/23 15:47, Chapman Flack wrote:
> On 2023-08-14 09:11, Erik Rijkers wrote:
>>   , '$' returning date[]
> 
> I certainly like that syntax better.
> 
> It's not that the "here's a null to tell you the type I want"
> is terribly unclear, but it seems not to be an idiom I have
> seen a lot of in PostgreSQL before now. Are there other places
> it's currently used that I've overlooked?

It has been used since forever in polymorphic aggregate final functions. 
  I don't mind it there, but I do not like it in general user-facing 
functions.

https://www.postgresql.org/docs/current/sql-createaggregate.html
-- 
Vik Fearing




Re: proposal: jsonb_populate_array

From
Vik Fearing
Date:
On 8/14/23 15:37, Pavel Stehule wrote:
> po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
> 
>> Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
>> odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>
>>   > with proposed function I can write
>>   >
>>   > select jsonb_populate_array(null:date[],
>>   > '["2023-07-13","2023-07-14"]'::jsonb)
>>   >
>> Not yet committed, but outstanding
>> SQL/JSON patches (v11) will let you do:
>>
>> select json_query(
>>       '["2023-07-13", "2023-07-14"]'::jsonb
>>     , '$' returning date[]
>> );
>>          json_query
>> -------------------------
>>    {2023-07-13,2023-07-14}
>> (1 row)
>>
>> That's (more or less) what you want, no?
>>
> 
> Yes, the functionality is exactly the same, but still maybe for  completeness
> the function json_populate_array can be nice.
> 
> In old API the transformations between json and row/record types is well
> covered, but for array, only direction array->json is covered

I don't think we should be extending the old API when there are Standard 
ways of doing the same thing.  In fact, I would like to see the old way 
slowly be deprecated.

> I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.
-- 
Vik Fearing




Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org> napsal:
On 8/14/23 15:37, Pavel Stehule wrote:
> po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
>
>> Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
>> odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>
>>   > with proposed function I can write
>>   >
>>   > select jsonb_populate_array(null:date[],
>>   > '["2023-07-13","2023-07-14"]'::jsonb)
>>   >
>> Not yet committed, but outstanding
>> SQL/JSON patches (v11) will let you do:
>>
>> select json_query(
>>       '["2023-07-13", "2023-07-14"]'::jsonb
>>     , '$' returning date[]
>> );
>>          json_query
>> -------------------------
>>    {2023-07-13,2023-07-14}
>> (1 row)
>>
>> That's (more or less) what you want, no?
>>
>
> Yes, the functionality is exactly the same, but still maybe for  completeness
> the function json_populate_array can be nice.
>
> In old API the transformations between json and row/record types is well
> covered, but for array, only direction array->json is covered

I don't think we should be extending the old API when there are Standard
ways of doing the same thing.  In fact, I would like to see the old way
slowly be deprecated.

> I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.

Unfortunately, these small extensions have zero chance to be available for users that use some cloud postgres.

 
--
Vik Fearing

Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


út 15. 8. 2023 v 5:12 odesílatel jian he <jian.universality@gmail.com> napsal:
\df jsonb_populate_record
                                 List of functions
   Schema   |         Name          | Result data type | Argument data
types | Type
------------+-----------------------+------------------+---------------------+------
 pg_catalog | jsonb_populate_record | anyelement       | anyelement,
jsonb   | func
(1 row)

manual:
> anyelement  Indicates that a function accepts any data type.
> For the “simple” family of polymorphic types, the matching and deduction rules work like this:
> Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type.

So jsonb_populate_record signature can handle cases like
jsonb_populate_record(anyarray, jsonb)? obviously this is a cast, it
may fail.
also if input is anyarray, so the output anyarray will have the same
base type as input anyarray.

It fails (what is expected - else be too strange to use function in name "record" for arrays)

 (2023-08-15 07:57:40) postgres=# select jsonb_populate_record(null::varchar[], '[1,2,3]');
ERROR:  first argument of jsonb_populate_record must be a row type

regards

Pavel

Re: proposal: jsonb_populate_array

From
Vik Fearing
Date:
On 8/15/23 07:53, Pavel Stehule wrote:
> út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org>
> napsal:
> 
>> On 8/14/23 15:37, Pavel Stehule wrote:
>>> po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
>>>
>>> I think so this can be +/- 40 lines of C code
>>
>> It seems to me like a good candidate for an extension.
> 
> Unfortunately, these small extensions have zero chance to be available for
> users that use some cloud postgres.

Then those people can use the Standard SQL syntax.  I am strongly 
against polluting PostgreSQL because of what third party vendors do and 
do not allow on their platforms.
-- 
Vik Fearing




Re: proposal: jsonb_populate_array

From
Pavel Stehule
Date:


út 15. 8. 2023 v 8:04 odesílatel Vik Fearing <vik@postgresfriends.org> napsal:
On 8/15/23 07:53, Pavel Stehule wrote:
> út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org>
> napsal:
>
>> On 8/14/23 15:37, Pavel Stehule wrote:
>>> po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
>>>
>>> I think so this can be +/- 40 lines of C code
>>
>> It seems to me like a good candidate for an extension.
>
> Unfortunately, these small extensions have zero chance to be available for
> users that use some cloud postgres.

Then those people can use the Standard SQL syntax.  I am strongly
against polluting PostgreSQL because of what third party vendors do and
do not allow on their platforms.

ok
 
--
Vik Fearing