PG官方文档:http://www.postgres.cn/docs/10/
1.行列转换函数
行转列:string_agg
select id,string_agg(name,',') from user group by id;
1 张三
1 李四
1 张三,李四
列转行:regexp_split_to_table
select id,regexp_split_to_table(name,',') from user;
1 张三,李四
1 张三
1 李四
regexp_split_to_table(replace(replace(replace(a.phones,'[',''),']',''),'},{','}_{'),'_')::jsonb->>'value' phone
2.JSON解析函数
用纯函数解决:
--数组内套json
SELECT json_extract_path(json_array_elements(cast(data as json)),'body') t FROM table;
--纯json
select json_extract_path(cast(data as json),'approveType') t from table;
select layout_json,json_array_elements(json_array_elements(layout_json::json -> 'rows')::json -> 'widgets')::json ->> 'name' t from dashboard_board where layout_json not like '%"type":"param"%';
3.替换函数
4.查询正在执行的sql
select * from pg_stat_activity where usename='dw' and query like '%job_info%' and state = 'active' order by xact_start desc limit 5;
5.中文排序
ORDER BY convert_to(user_name,'GBK')