Horton
Menu

Thread

  1. proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-14T03:51:57Z

    Hi

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

https://stackoverflow.com/questions/76894960/unable-to-assign-text-value-to-variable-in-pgsql/76896112#76896112

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

  2. Re: proposal: jsonb_populate_array

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-08-14T09:32:24Z

    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)

  3. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-14T12:51:43Z

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

  4. Re: proposal: jsonb_populate_array

    Erik Rijkers <er@xs4all.nl> — 2023-08-14T13:11:27Z

    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

  5. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-14T13:37:04Z

    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 think so this can be +/- 40 lines of C code





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

  6. Re: proposal: jsonb_populate_array

    Chapman Flack <chap@anastigmatix.net> — 2023-08-14T13:47:27Z

    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

  7. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-14T14:26:35Z

    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
>

  8. Re: proposal: jsonb_populate_array

    jian he <jian.universality@gmail.com> — 2023-08-15T03:12:16Z

    \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.

  9. Re: proposal: jsonb_populate_array

    Vik Fearing <vik@postgresfriends.org> — 2023-08-15T05:44:36Z

    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

  10. Re: proposal: jsonb_populate_array

    Vik Fearing <vik@postgresfriends.org> — 2023-08-15T05:48:40Z

    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

  11. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-15T05:53:33Z

    ú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
>
>

  12. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-15T05:59:05Z

    ú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

  13. Re: proposal: jsonb_populate_array

    Vik Fearing <vik@postgresfriends.org> — 2023-08-15T06:04:54Z

    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

  14. Re: proposal: jsonb_populate_array

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-08-15T06:26:29Z

    ú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
>
>