APIJSON [xxx] 您好，麻烦请教，针对两个字段相加的情况，应该怎么编写json字符串呢？

Description

SELECT ( subquery1.sumCount + subquery2.docCount ) AS total FROM ( SELECT SUM( doc_count ) AS sumCount FROM apijson_java.Base_data_count WHERE data_date > 20230604 AND data_type=1 LIMIT 1 ) AS subquery1, ( SELECT doc_count AS docCount FROM apijson_java.Base_count WHERE data_type = 1 LIMIT 1 ) AS subquery2; 例如针对上面的这条sql，该怎么编写json呢？

Jun 30 '23 02:06 zhangyiyi909

你提供的 SQL 语句，按实际表名和字段名改了后，在 MySQL 8.0.32 社区版执行报错，你在什么数据库及版本能把以上 SQL 成功执行返回结果？把截屏发出来看看

SELECT (subquery1.sumCount + subquery2.docCount) AS total FROM (
  SELECT sum(userId) AS sumCount FROM sys.Moment WHERE date > '2020-01-01 00:00:00' AND userId=82001 LIMIT 1
) AS subquer1, (
  SELECT userId AS docCount FROM sys.Comment WHERE toId = 0 LIMIT 1
) AS subquer2;

Jul 02 '23 05:07 TommyLemon

如果只是相加，直接 @column + @raw 就行了，只是你的 SQL 中包含主表、副表两个子查询，目前看起来数据库都不支持 SELECT 子查询字段

{
    "[]": {
        "Moment": {
            "@column": "id,userId;(id + userId):sum",
            "@raw": "@column", // 必须
            "@explain": true
        }
    }
}

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22Moment%22:{%22@column%22:%22id%2CuserId%3B(id%20%2B%20userId)%3Asum%22,%22@raw%22:%22%40column%22,%22@explain%22:true}}}

Jul 02 '23 05:07 TommyLemon

我用的mysql版本是5.7

Jul 03 '23 01:07 zhangyiyi909

我的目的是Base_data_count(增量表)中的doc_count累加某个日志之后的数据，然后再加上Base_count（底表）中的doc_count

Jul 03 '23 01:07 zhangyiyi909

SELECT (SELECT SUM(doc_count) FROM apijson_java.Base_data_count WHERE data_date > 20230604 AND data_type = 1) + (SELECT doc_count FROM apijson_java.Base_count WHERE data_type = 1 LIMIT 1) AS total; 这个语句能体现我的意图

Jul 03 '23 01:07 zhangyiyi909

SELECT (SELECT SUM(doc_count) FROM apijson_java.Base_data_count WHERE data_date > 20230604 AND data_type = 1) + (SELECT doc_count FROM apijson_java.Base_count WHERE data_type = 1 LIMIT 1) AS total; 这个语句能体现我的意图 APIJSON执行sql语句, 你这种统计, 如果担心性能, 外层加缓存即可 https://github.com/APIJSON/APIJSON-Demo/tree/master/APIJSON-Java-Server/APIJSONDemo-RawSQL

Jul 04 '23 03:07 cloudAndMonkey

(SELECT doc_count FROM apijson_java.Base_count WHERE data_type = 1 LIMIT 1) AS total; @zhangyiyi909 目前子查询只支持在主表用，JOIN 的副表不支持。不过可以分别获取所需字段后，在应用层计算

{
    "Comment": {
        "@column": "toId,momentId",
        "userId": 82001,
        "date>": "2020-01-01 00:00:00"
    },
    "Moment": {
        "@column": "sum(userId):sum",
        "userId": 82001,
        "date>": "2020-01-01 00:00:00"
    },
    "sum()": "plus(Comment/toId,Moment/sum)" // 后端写一个远程函数，或者直接前端算
}

Jul 08 '23 09:07 TommyLemon

已支持，忘了目前所有副表在 CROSS/LEFT/RIGHT JOIN 时都是套了层子查询，如果主表用 @from@:{} ，那也会套一层子查询。

不过之前不支持在 @column: value 中自定义字段名对应的表名，只能自动分配，对于这样的需求，自动分配错了表名。

现在支持了，可以通过源码依赖，或者等发新版 https://github.com/Tencent/APIJSON/commit/247f149c406c10c89840fbcfd0e2855d44e719b5

{
    "[]": {
        "count": 1,
        "join": {
            "*/Moment": {
                "@column": "(Comment.id + sum):result",
                "@raw": "@column"
            }
        },
        "Comment": {
            "@explain": true,
            "@from@": {
                "from": "Comment",
                "Comment": {
                    "@column": "id",
                    "userId": 82001,
                    "date>": "2020-01-01 00:00:00"
                }
            }
        },
        "Moment": {
            "@column": "sum(id):sum",
            "userId": 82001,
            "date>": "2020-01-01 00:00:00"
        }
    }
}

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Flocalhost%3A8080%2Fget&json={%22[]%22:{%22count%22:1,%22join%22:{%22*/Moment%22:{%22@column%22:%22(Comment.id%20%2B%20sum)%3Aresult%22,%22@raw%22:%22%40column%22}},%22Comment%22:{%22@explain%22:true,%22@from@%22:{%22from%22:%22Comment%22,%22Comment%22:{%22@column%22:%22id%22,%22userId%22:82001,%22date%3E%22:%222020-01-01%2000%3A00%3A00%22}}},%22Moment%22:{%22@column%22:%22sum(id)%3Asum%22,%22userId%22:82001,%22date%3E%22:%222020-01-01%2000%3A00%3A00%22}}}&random=User%2Fid%3A%20RANDOM_INT(82001%2C%2082020)%20%2F%2F%20%E9%9A%8F%E6%9C%BA%E6%95%B4%E6%95%B0%0A%5B%5D%2Fcount%3A%20RANDOM_IN(5%2C%2010%2C%20%27s%27%2C%20false%2C%20%5B%5D%2C%20%7B%7D)%20%2F%2F%20%E9%9A%8F%E6%9C%BA%E5%8F%96%E5%80%BC%0A%5B%5D%2Fpage%3A%20Math.round(5*Math.random())%20%2F%2F%20%E9%80%9A%E8%BF%87%E4%BB%A3%E7%A0%81%E6%9D%A5%E8%87%AA%E5%AE%9A%E4%B9%89%0A%40explain%3A%20ORDER_IN(true%2C%20false)%20%2F%2F%20%E9%A1%BA%E5%BA%8F%E5%8F%96%E5%80%BC%0A%2F%2F%20%E4%BB%8E%E6%95%B0%E6%8D%AE%E5%BA%93%E9%9A%8F%E6%9C%BA%E5%8F%96%E5%80%BC%20%20%5B%5D%2FComment%2FtoId%3A%20RANDOM_DB()%0A%0A%2F%2F%20%E5%9B%9E%E8%BD%A6%E6%99%BA%E8%83%BD%E7%94%9F%E6%88%90%E3%80%82%E6%B3%A8%E9%87%8A%E5%8F%AF%E7%9C%81%E7%95%A5%EF%BC%8C%E6%9C%AA%E7%9C%81%E7%95%A5%E5%88%99%E5%89%8D%E9%9D%A2%E5%BF%85%E9%A1%BB%E7%A9%BA%E6%A0%BC%EF%BC%9B%E6%B8%85%E7%A9%BA%E6%96%87%E6%9C%AC%E5%86%85%E5%AE%B9%E5%8F%AF%E6%9F%A5%E7%9C%8B%E8%A7%84%E5%88%99%E3%80%82%0A%0A%2F%2F%20%23%23%20%E5%BF%AB%E6%8D%B7%E9%94%AE%0A%2F%2F%20Ctrl%20%2B%20I%20%E6%88%96%20Command%20%2B%20I%20%E6%A0%BC%E5%BC%8F%E5%8C%96%E6%96%87%E6%9C%AC%EF%BC%8C%E6%B8%85%E9%99%A4%E6%89%80%E6%9C%89%E6%B3%A8%E9%87%8A%E5%92%8C%E6%97%A0%E6%95%88%E7%A9%BA%E6%A0%BC%E3%80%81%E6%8D%A2%E8%A1%8C%E7%AD%89%EF%BC%9B%0A%2F%2F%20Ctrl%20%2B%20%2F%20%E6%88%96%20Command%20%2B%20%2F%20%E5%AF%B9%E9%80%89%E4%B8%AD%E8%A1%8C%20%E6%96%B0%E5%A2%9E%E8%A1%8C%E6%B3%A8%E9%87%8A%20%E6%88%96%20%E5%8F%96%E6%B6%88%E8%A1%8C%E6%B3%A8%E9%87%8A%EF%BC%9B%0A%2F%2F%20Ctrl%20%2B%20D%20%E6%88%96%20Command%20%2B%20D%20%E5%88%A0%E9%99%A4%20%E9%80%89%E4%B8%AD%E8%A1%8C%EF%BC%9B%0A%2F%2F%20Ctrl%20%2B%20S%20%E6%88%96%20Command%20%2B%20S%20%E4%BF%9D%E5%AD%98%E5%BD%93%E5%89%8D%E8%AF%B7%E6%B1%82%EF%BC%9B&setting={%22requestVersion%22:%22%22,%22requestCount%22:1,%22isTestCaseShow%22:false,%22isCrossEnabled%22:false,%22isMLEnabled%22:true,%22isDelegateEnabled%22:false,%22isPreviewEnabled%22:false,%22isEncodeEnabled%22:true,%22isEditResponse%22:false,%22page%22:0,%22count%22:50,%22testCasePage%22:0,%22testCaseCount%22:50,%22testRandomCount%22:1,%22randomPage%22:0,%22randomCount%22:50,%22randomSubPage%22:0,%22randomSubCount%22:50}

Jul 08 '23 11:07 TommyLemon

这个需求，FROM (主表子查询) 没有必要，直接 FROM 主表就行了

{
    "[]": {
        "count": 1,
        "join": {
            "*/Moment": {
                "@column": "(Comment.id + sum):result",
                "@raw": "@column"
            }
        },
        "Comment": {
            "@explain": true,
            "@column": "id",
            "userId": 82001,
            "date>": "2020-01-01 00:00:00"
        },
        "Moment": {
            "@column": "sum(id):sum",
            "userId": 82001,
            "date>": "2020-01-01 00:00:00"
        }
    }
}

Jul 09 '23 10:07 TommyLemon

(SELECT doc_count FROM apijson_java.Base_count WHERE data_type = 1 LIMIT 1) AS total; @zhangyiyi909 目前子查询只支持在主表用，JOIN 的副表不支持。不过可以分别获取所需字段后，在应用层计算
{
    "Comment": {
        "@column": "toId,momentId",
        "userId": 82001,
        "date>": "2020-01-01 00:00:00"
    },
    "Moment": {
        "@column": "sum(userId):sum",
        "userId": 82001,
        "date>": "2020-01-01 00:00:00"
    },
    "sum()": "plus(Comment/toId,Moment/sum)" // 后端写一个远程函数，或者直接前端算
}

这种方式我之前试过，貌似不支持吧， public int plus(@NotNull JSONObject curObj, String sumCount, String docCount) throws Exception 在这个方法里面，获取到的sumCount的值就是原始的Comment

这种方式应该是行不通的，我之前试过 public double plus(@NotNull JSONObject curObj, String i0, String i1) { return curObj.getDoubleValue(i0) + curObj.getDoubleValue(i1); } 在这个方法里面，调用远程函数传进来的curObj是原始的json串，而不是计算出来的结果吧，即 { "Comment": { "@column": "toId,momentId", "userId": 82001, "date>": "2020-01-01 00:00:00" }, "Moment": { "@column": "sum(userId):sum", "userId": 82001, "date>": "2020-01-01 00:00:00" }, "sum()": "plus(Comment/toId,Moment/sum)" // 后端写一个远程函数，或者直接前端算 }

Jul 10 '23 01:07 zhangyiyi909

@zhangyiyi909 curObj 就是当前对象，不是 JSON 字符串，你断点调试下。传 "Comment/toId" 给 i0 然后 curObj.getDoubleValue(i0) 明显不行，因为 curObj 没有对应的键值对 "Comment/toId": value 应该用 / 分割里面的 key，逐层 get 出来。当然如果只是自己测试的话，可以 curObj.getJSONObject("Comment").getInteger("toId") 试试效果

Jul 16 '23 14:07 TommyLemon

这个curObj是初始传过来的json对象

Jul 17 '23 01:07 zhangyiyi909

在调用远程函数时，Comment/toId,Moment/sum这两个值是没有计算出来结果的

Jul 17 '23 01:07 zhangyiyi909

这个是因为远程函数比子对象 Comment, Moment 更早执行导致， "sum()" 改成 "sum+()" 降低执行优先级，可设置为在子对象之后解析 https://github.com/Tencent/APIJSON/blob/master/Document.md#3.2

Jul 17 '23 13:07 TommyLemon

多谢，改变优先级之后确实可以

Jul 20 '23 07:07 zhangyiyi909