Postgres Pro
Downloads
Home   >   mailing lists

Thread: Should we support casting between ARRAYs and JSON(B)?

Should we support casting between ARRAYs and JSON(B)?

From
Aleksander Alekseev
Date:
Hi hackers,

While reviewing another patch [1] 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]: https://postgr.es/m/CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw%40mail.gmail.com

-- 
Best regards,
Aleksander Alekseev

Re: Should we support casting between ARRAYs and JSON(B)?

From
Marcos Pegoraro
Date:
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