Should we support casting between ARRAYs and JSON(B)?
Hi hackers,
While reviewing another patch [1]/messages/by-id/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw@mail.gmail.com I came to an idea to try something stupid:
=# select '{1,2,3}' :: int[];
int4
---------
{1,2,3}
=# select '{1,2,3}' :: int[] :: jsonb[];
ERROR: cannot cast type integer[] to jsonb[]
=# select '[1,2,3]' :: jsonb;
jsonb
-----------
[1, 2, 3]
=# select '[1,2,3]' :: jsonb :: int[];
ERROR: cannot cast type jsonb to integer[]
Does anyone believe that this should work and/or would be convenient
if it worked? I can imagine cases when one would like to use array_*
functions for JSON(B) although personally I didn't encounter such a
case (yet?).
Thoughts?
[1]: /messages/by-id/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw@mail.gmail.com
--
Best regards,
Aleksander Alekseev
Em seg., 28 de out. de 2024 às 14:06, Aleksander Alekseev <
aleksander@timescale.com> escreveu:
=# select '[1,2,3]' :: jsonb :: int[];
I think would be useful, cast int[] to json is not hard
select to_json('{1,5,9,12}'::int[]);
but json array to int[] is not that easy.
select js,
js->'items',
translate(js->>'items','[]','{}')::int[],
5 = any(translate(js->>'items','[]','{}')::int[])
--This one would be cool, doesn't need translate or any other trick
--5 = any(js->'items'::int[])
from (select jsonb_build_object('items','{1,5,9,12}'::int[])) x(js);
So, if you cast one way, you can do both ways.
regards
Marcos