Thread: proposal: jsonb_populate_array
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
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)
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)
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
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
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
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
\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.
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
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
ú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
ú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
ERROR: first argument of jsonb_populate_record must be a row type
regards
Pavel
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
ú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