在子查询mysql上使用JSON_TABLE交叉联接

Question

我不知道如何在MySQL8.0中只在另一个表的一小部分上使用JSON_TABLE，如果这一部分的选择涉及连接或子查询：

让我们从一个表predictions开始，它包含预测的值以及一些外键

SELECT * FROM predictions

id | ml_model_id | value
+--+-------------+------ 
1  | 1           | [{"class": "dog", "confidence": 1}, {"class": "cat", "confidence": 0.03}]

下面是整个表的选择，它工作得很好：

SELECT * 
    FROM predictions,
         JSON_TABLE(
             predictions.value,
              "$[*]" 
              COLUMNS (class VARCHAR(30) PATH "$.class",
                       confidence FLOAT PATH "$.confidence")
            ) myjson

原始问题:子查询不能用作表来构建JSON_TABLE

但是，如果我用子查询替换predictions表，这将不再起作用

SELECT * FROM (
    SELECT * FROM predictions
    WHERE ml_model_id = 1
    # Do any type of filtering here, or even none at all
    ) preds,
    JSON_TABLE(
        preds.value,
        "$[*]"
        COLUMNS (class VARCHAR(30) PATH "$.class",
                       confidence FLOAT PATH "$.confidence")
            ) myjson
        )

我将得到一个SQL错误1210“JSON_TABLE的参数不正确”。

有限的解决方法:如果筛选是针对表列的，则可以使用WHERE...

我的第一个想法是在子查询之外进行过滤，这在一定程度上是可行的：

SELECT * FROM predictions, 
    JSON_TABLE(...) myjson
WHERE predictions.ml_model_id = 1

这个解决方案行得通，对我来说已经不够优雅了，因为它意味着predictions和JSON_TABLE之间的,是隐式横向联接，而不是交叉联接(编辑:请注意，即使使用CROSS JOIN而不是,也会进行隐式横向联接)

...But如果您的过滤涉及连接，则它不起作用

然而，如果不是我想要通过连接过滤的地方，我会得到一个更隐蔽的错误：

SELECT * FROM predictions, 
    JSON_TABLE(...) myjson
   INNER JOIN ml_models
       ON ml_models.id = predictions.ml_model_id
       AND ml_models.usage = "classification"

将引发一个SQL ERROR [1054] Unknown column 'predictions.ml_model_id' in 'on clause'...为什么找不到这一列？在不使用内部连接的情况下运行，实际上会返回它！

似乎在所有情况下都适用的不优雅的解决方法:在WHERE子句中使用EXISTS

简单地将INNER JOIN替换为WHERE EXISTS是可行的，但是以这种方式工作确实是违反直觉的。

SELECT * FROM predictions, 
    JSON_TABLE(...) myjson
   WHERE EXISTS (SELECT 1 FROM ml_models
       WHERE ml_models.id = predictions.ml_model_id
       AND ml_models.usage = "classification")

在JSON_TABLE上，有什么我不理解的地方使我无法在它上面使用子查询吗？

Answer 1

通过在JSON_TABLE中再次将JSON字段转换为JSON，我最终成功地将其作为连接的第一部分使用子查询工作

SELECT * FROM (
    SELECT * FROM predictions
    WHERE ml_model_id = 1
    # Do any type of filtering here, or even none at all
    ) preds,
    JSON_TABLE(
        CAST(preds.value AS JSON),
        "$[*]"
        COLUMNS (class VARCHAR(30) PATH "$.class",
                       confidence FLOAT PATH "$.confidence")
            ) myjson
        )

然而，我更不明白为什么这会起作用，所以如果有人能解释为什么它会这样工作，而不是没有演员的话，我会继续问这个问题。