SELECT
t0.startDate,
t2.employeeNo,
t0.tab1,
t0.tab2,
t0.displayName,
CONCAT(t2.lastName,t2.firstName ) AS employeeName,
belongDeptNum
FROM
(SELECT
formdata.contentJson->'$.billInfoList' AS tab1,
formdata.contentJson->'$.datagrid2List' AS tab2,
JSON_UNQUOTE(json_extract(json_extract(formdata.contentJson,'$.formInfo'),'$.startDate')) AS startDate,
JSON_UNQUOTE(json_extract(formdata.contentJson,'$.costAttributiondefault')) AS displayName,
JSON_UNQUOTE(json_extract(json_extract(formdata.contentJson,'$.sectioncols4'),'$.belongDeptNum')) AS belongDeptNum,
JSON_UNQUOTE(json_extract(json_extract(formdata.contentJson,'$.sectioncols15'),'$.applicationName')) AS applicationName
FROM
FormData formdata
WHERE
JSON_UNQUOTE(json_extract(json_extract(formdata.contentJson,'$.formInfo'),'$.folio')) = #{folio, jdbcType=VARCHAR}
AND is_del = 0) AS t0
LEFT JOIN com_paasit_pai_core_employeeObj t2
ON t2.id = t0.applicationName
其中这formdata表中的contentJson字段是一个json格式的表格
上面的tab1是行程和出差补助这两大列的数据 tab2是票据的数据 返回的 这两个格式是json格式的数组字符串 ,然后json格式字符串转对象就很简单啦
//将表格json数据转成集合
JSONArray jsonarray1 = JSONArray.fromObject(queryObj.getTab1());
List<FormF05RespS01> tab1 = (List<FormF05RespS01>)JSONArray.toCollection(jsonarray1, FormF05RespS01.class);