Postgres - 从数组中选择元素

问题描述：

在我的表中我有一个名为facebook的列作为text []类型。例如，一行是：Postgres - 从数组中选择元素

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

现在我想只SELECT 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订购方式？

答

你在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)

Postgres - 从数组中选择元素

相关推荐