1. 使用case when 和获取json里面的属性的sql用例
菜鸡的我sql写的少,pgsql的某些特性太强大了,记一下, 以后碰到了类的情况还能抄一抄代码。
with tmp as ( select task.*,(task_json::json->>'params')::json->>'outputTable' as outputTable,ins.show_name as processInstanceName,
cast(((task_json::json->>'params')::json->>'driverCores') as INTEGER)+cast ( ((task_json::json->>'params')::json->>'executorCores') as INTEGER ) as cpuCores,
case when ((task_json::json->>'params')::json->>'driverMemory') ~ 'M'
then
cast (substring((task_json::json->>'params')::json->>'driverMemory',
0,
length((task_json::json->>'params'