Thread: Should we support casting between ARRAYs and JSON(B)?
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
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[])
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[])
--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