postgresql-sort array by words in each elements

问题:

There is string array

ARRAY['CAT','CAT DOG CAT','DOG Cat']

Now i want to sort this array on count of words in each element. I have tried but cannot get any success.

I want this output

ARRAY['CAT DOG CAT','DOG CAT','Cat']

How i can do this?

回答1:

This does feel rather clumsy, but I can't think of a simpler solution right now:

with val (col) as (
  values (ARRAY['CAT','CAT DOG CAT','DOG Cat'])
), word_list as (
  select unnest(col) as pc
  from val
), wc as (
  select array_length(string_to_array(pc, ' '),1) as word_count, pc
  from word_list
)
select array_agg(pc order by word_count desc)
from wc;