CODEWITHSUNDEEP
postgresqldatatable
Daniel Koczuła
Daniel Koczuła

Reputation: 1034

Postgres order by array value

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:

enter image description here

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:

enter image description here

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?

Upvotes: 1

Views: 417

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

cannot cast type text[] to numeric

You need to cast to numeric[]:

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

Upvotes: 2

Related Questions