php - Postgres - 从数组中选择元素

Question

在我的表格中，我有一个名为 facebook 的列，类型为 text[]。例如一行是:

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

现在我试图只选择 total_count。我一直在尝试一切并以此结束:

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

但是我得到一个错误:运算符不存在:记录 ->> 未知\n第 1 行:

有什么想法吗？

//编辑

现在我明白了

$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";

有没有办法将 total_count 添加到 ORDER？

Answer 1

你的facebook属性中有一个文本数组数组，所以to_json也将它转换为多维数组，因此你需要对数组进行操作，例如:

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)