CODEWITHSUNDEEP
postgresql
symchiladas
symchiladas

Reputation: 23

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

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:

Upvotes: 1

Views: 244

Answers (1)

Hambone
Hambone

Reputation: 16377

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.

Upvotes: 1

Related Questions