Postgres order by array value

Question

In my table I've got a column named facebook with type text[] with array in it. For example:

{{total_count,26861},{comment_count,94},{comment_plugin_count,0},{share_count,26631},{reaction_count,136}}

I'm using my database with datatables and when I'm trying to sort my table by facebook I've got this:

And this is wrong. So I'm trying to get from this array only total_count as numeric value. Now I've got this:

regexp_matches(array_to_string(facebook, ' '), '(\d+).*')

But this returns arrays, for example:

So I've added ::numeric

regexp_matches(array_to_string(facebook, ' '), '(\d+).*')::numeric

but I'm getting an error:

cannot cast type text[] to numeric

Any ideas how to fix it?

Lukasz Szozda · Accepted Answer · 2017-10-20 19:34:28Z

2

cannot cast type text[] to numeric

You need to cast to numeric[]:

regexp_matches(array_to_string(facebook, ' '), '(\d+).*')::numeric[]

answered Oct 20, 2017 at 19:34

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Answer 1