CODEWITHSUNDEEP
phpsqlpostgresqlpostgresql-9.3
Daniel Koczuła
Daniel Koczuła

Reputation: 1034

Postgres - select element from array

In my table I've got a column named facebook as type text[]. For example one row is:

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

Now I'm trying to SELECT only total_count. I've been trying everythin and ended with this:

SELECT json_each(to_json(facebook))->>'total_count' AS total_count"

But I'm getting an error: operator does not exist: record ->> unknown\nLINE 1:

Any ideas?

//edit

Now I've got this

$select = "WITH fejs AS ( select json_array_elements(to_json(facebook)) e FROM $table ), arr AS ( select e->1 as total_count FROM fejs WHERE e->>0 = 'total_count') SELECT ".implode(", ", SSP::pluckas($columns))."
         , count(*) OVER() AS full_count
         FROM $table             
         $where
         $order
         $limit";

Is there a way that I can add total_count to ORDER?

Upvotes: 1

Views: 1881

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51416

you have array of text arrays in facebook attribute, so to_json converts it to multidimensional array too, thus you need to operate arrays, eg:

t=# with t(facebook) as (values('{{total_count,26861},{comment_count,94},{comment_plugin_count,0},{share_count,26631},{reaction_count,136}}'::text[]))
, arr as (select json_array_elements(to_json(facebook)) e from t)
select e->1 as total_count from arr where e->>0 = 'total_count';
 total_count
-------------
 "26861"
(1 row)

Upvotes: 1

Related Questions