• 將行記錄轉為jsonb
  row_to_json(表名或別名)
• 將行記錄集轉為json數組 （jsonb)

select json_agg(row_to_json(t) order by t.task_name) into v_next_taskfrom 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_idFROM 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 rnFROM dyna_flow_logWHERE bill_id = '1362327695287267328' AND cycle = 1
) sub
-- 只取 create_date 最大的那條記錄
WHERE rn = 1;