Postgresql几个常用的json操作

• 将行记录转为jsonb
  row_to_json(表名或别名)
• 将行记录集转为json数组 （jsonb)

select json_agg(row_to_json(t) order by t.task_name) into v_next_task
   from dyna_flow_task t where t.zid=v_template_id and t.level=v_next_level ;

• 访问json字段，用->>‘字段名’

 SELECT DISTINCT ON ((value ->> 'user_id')) 
		           value->>'loginname' AS loginid,
		           value->>'user_id' AS user_id
		    FROM jsonb_array_elements(
		        fn_flow_getappr_users_by_task_ids(array[v_last_node_id])::jsonb
		    ) AS elem(value)

• 将json数组转为记录集
  用jsonb_array_elements

SELECT elem ->> 'id'
    FROM jsonb_array_elements((select * from fn_flow_getnexttask_by_instanceid('1362327695287267328') AS next_task)::jsonb) AS elem

• 单个json转为数组型json,用jsonb_build_array

SELECT jsonb_build_array(row_to_json(sub))
FROM (
    SELECT 
        create_by,
        ROW_NUMBER() OVER (PARTITION BY bill_id, cycle ORDER BY create_date DESC) as rn
    FROM dyna_flow_log
    WHERE bill_id = '1362327695287267328' AND cycle = 1
) sub
-- 只取 create_date 最大的那条记录
WHERE rn = 1;