PosgresQL中数组中的数组处理

您可以使用一个子查询,在该子查询中,JSON数组被转换为set jsonb_array_elements_text() 然后用 array_agg() 沿途浇铸。

SELECT (SELECT array_agg(jae.e::decimal)
               FROM jsonb_array_elements_text("Path"->'points'->'coordinates') jae(e)) points
FROM "Route";

Blog

SELECT array_agg(t) 
FROM ( 
       SELECT REPLACE(REPLACE(REPLACE(UNNEST(a), '[', ''), ']', ''),' ','')::decimal AS t, ROW_NUMBER() OVER (ORDER BY 1) AS r 
       FROM ( 
              SELECT STRING_TO_ARRAY(jsonb_array_elements_text("Path"->'points'->'coordinates'), ',') a 
              FROM "Route" 
            ) t 
      ) t 
Group by r 
order by r;