同一个sql,在mysql8执行没有问题,但是在dataease的数据集写sql执行返回为空:
SELECT
b.name AS ‘姓名’,
DATE_FORMAT(a.create_time, ‘%Y-%m-%d’) AS ‘提交日期’,
JSON_UNQUOTE(JSON_EXTRACT(a.content, ‘$.children.result[0].name’)) AS ‘项目1名称’,
JSON_UNQUOTE(JSON_EXTRACT(a.content, ‘$.children.result[0].value’)) AS ‘项目1内容’,
JSON_UNQUOTE(JSON_EXTRACT(a.content, ‘$.children.result[1].name’)) AS ‘项目2名称’,
JSON_UNQUOTE(JSON_EXTRACT(a.content, ‘$.children.result[1].value’)) AS ‘项目2内容’
FROM erate_activity a
LEFT JOIN stu_teacher b ON a.create_by = b.id
WHERE a.deleted = 0;
项目1名称开始的四个字段没有值返回。
如果格式换成以下形式,则直接报错,但在数据库直接执行,也是可以正常返回。
a.content
->“$.children.result[0].name” ‘项目1名称’,
a.content
->“$.children.result[0].value” ‘项目1内容’,
a.content
->“$.children.result[1].name” ‘项目2名称’,
a.content
->“$.children.result[1].value” ‘项目2内容’,
报错信息:net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: “->” “->” at line 2, column 13. Was expecting one of: “.” “::” “[” “^” <ST_SEMICOLON>