Postgres - select element from array

Question

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?

Vao Tsun · Accepted Answer · 2017-10-20 07:17:53Z

1

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)

answered Oct 20, 2017 at 7:17

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Postgres - select element from array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related