MySQL left join on an exploded dataset-CodePudding

I have a table in MYSQL with a json array, if I explode the array using JSON_TABLE to give me a row for every entry in the array I can't seem to left join onto that table any more my query that I am using is:

SELECT
    *
FROM tbl_emp_bin_scans tebs,
     JSON_TABLE(tebs.fkEmp, "$[*]" COLUMNS(empID VARCHAR(100) PATH "$" ERROR ON ERROR)) AS tEmpID
LEFT JOIN tbl_blocks tb on tb.id = tebs.fkBlock
WHERE
        DATE(captured) = DATE('2022-04-09')

The error that I'm getting is:

[42S22][1054] Unknown column 'tebs.fkBlock' in 'on clause'

At the moment I'm using the workaround:

SELECT *
FROM (
    SELECT
        *
    FROM tbl_emp_bin_scans tebs,
        JSON_TABLE(tebs.fkEmp, "$[*]" COLUMNS(empID VARCHAR(100) PATH "$" ERROR ON ERROR)) AS tEmpID
    WHERE
        DATE(captured) = DATE('2022-04-09')
) bins
LEFT JOIN tbl_blocks tb on tb.id = bins.fkBlock

tbl_emp_bin_scans

[
  { "id": 916, "captured": "2022-04-09 10:49:18", "fkEmp": "[43735]", "fkBlock": 5184, },
  { "id": 917, "captured": "2022-04-09 10:49:51", "fkEmp": "[45092,43735,45021]", "fkBlock": 5184, },
  { "id": 918, "captured": "2022-04-09 10:54:42", "fkEmp": "[45021]", "fkBlock": 5184, },
  { "id": 919, "captured": "2022-04-09 10:55:28", "fkEmp": "[45041,45021]", "fkBlock": 5184, },
  { "id": 920, "captured": "2022-04-09 10:57:25", "fkEmp": "[45023]", "fkBlock": 5184, }
]

tbl_blocks

[
  { "id": 5184, "description": "BLK8", "fkVariety": 266, "fkFarm": 90, "updated": "2022-04-07 13:02:19" }
]

If anyone has any clue as to why I can't join to fkBlock without having to do a subquery it would really be appreciated

CodePudding user response：

Comma priority is less than any JOIN priority (JSON_TABLE works normally because it is LATERAL hiddenly)... Never use a comma, replace it with CROSS JOIN.

SELECT      *
FROM        tbl_emp_bin_scans tebs
CROSS JOIN  JSON_TABLE(tebs.fkEmp, "$[*]" COLUMNS(empID VARCHAR(100) PATH "$" ERROR ON ERROR)) AS tEmpID
LEFT JOIN   tbl_blocks tb on tb.id = tebs.fkBlock
WHERE       captured >= '2022-04-09' AND captured < '2022-04-09'   INTERVAL 1 DAY