SQL查询JSON格式的数据

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);