How to select a random array from list of array objects based on index?

Question

In my PostgreSql procedure code, I set variables with random values. In this specific example, I am attempting to randomly pick from a list of values when setting my variables. My struggle is trying to pick a random list from a list of lists using the approach below; the random list selected should be a valid array or can be cast into a valid array.

DO 
$EG$ 
DECLARE
  try1 text := (SELECT (ARRAY['item10','item20','item30'])[1]);
  try2 text := (SELECT (ARRAY['item10','item20','item30'])[floor(random() * 3 + 1)]);
  try3 text := (SELECT (ARRAY['item10,item11','item20,item21','item30,item31'])[floor(random() * 3 + 1)]);
  try4 text := (SELECT (ARRAY['''item10'',''item11''','''item20'',''item21''','''item30'',''item31'''])[floor(random() * 3 + 1)]);
  try5 text := (SELECT (ARRAY[ARRAY['item10','item11'],ARRAY['item20','item21'],ARRAY['item30','item31']])[floor(random() * 3 + 1)]);
BEGIN
  -- 1.1 & 2.1 examples below establish the pattern context
  RAISE INFO 'try1.1 (use index).......: %', try1;
  RAISE INFO 'try2.1 (use random index): %', try2;
  -- 3.1 through 5.1 are attempts 
  RAISE INFO 'try3.1 (string_to_array).: %', string_to_array(try3,',');
  -- RAISE INFO 'try3.2 (cast to text[])..: %', try3::text[]; -- fails
  RAISE INFO 'try4.1 (string_to_array).: %', string_to_array(try4,',');
  -- RAISE INFO 'try4.2 (cast to text[])..: %', try4::text[]; -- fails
  RAISE INFO 'try5.1 (array of arrays).: %', try5;
END
$EG$;

Results from the above script:

INFO:  try1.1 (use index).......: item10
INFO:  try2.1 (use random index): item20
INFO:  try3.1 (string_to_array).: {item10,item11}
INFO:  try4.1 (string_to_array).: {'item20','item21'}
INFO:  try5.1 (array of arrays).: <NULL>

Notes:

try3.2 (commented out) fails with malformed array literal: "item10,item11"
try4.1 is the closest to what I'm striving for but other established arrays in my table look like this: {"item20","item21"}
try4.2 (commented out) fails with malformed array literal: "'item20','item21'"
try5.1 is an array of arrays, but returns null

Hambone · Accepted Answer · 2022-10-01 04:08:37Z

1

So some of your test inputs aren't really arrays -- they are text that may look like an array, but you are taking the rendering of arrays notation:

{foot,ball}

Which literally will be a string surrounded by curly brackets. If you are referring to an array column, then for sure this will act as shorthand and parse it properly, but unless you do this or cast it, that's just text.

So in other words:

array['one', 'two', 'three']  -- good array
'{one,two,three}'::text[]     -- also works
'{one,two,three}'             -- that's a string unless contextually
                                 applied to an array column

But that has nothing to do with my solution.

My suggestion -- take the array of arrays and unnest it, order it randomly, and then take however many values you want and aggregate them back into an array.

This may be more CTE steps than necessary but I couldn't get random to work otherwise:

with test_array as (   -- this is just sample data, 2d array
  select array[
    array['a1','a2','a3','a4','a5'],
    array['b100','b200','b300','b400','b500']] as foot
),
shuffle as (
  select unnest (foot) as ball
  from test_array
),
final as (
  select ball
  from shuffle
  order by random()
)
select (array_agg(ball))[1:3] -- example 3 values
from final

If you run this over and over you will see it gives you different results each time.

answered Oct 1, 2022 at 4:08

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

symchiladas Over a year ago

This answer is definitely getting closer but the results are munging together the values from the array at index 1 and the array at index 2, but I am trying to randomly return the full contents of only one array. Take for example the test data in try5 from my original question, I have an array which contains three arrays; my struggle is to randomly return the full contents of only one of the three arrays. If you run my code, you will see that the results from try4.1 are achieving this, but the structure of the array seems incorrect.

Collectives™ on Stack Overflow

How to select a random array from list of array objects based on index?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related