json - 为什么 JSON_TABLE() 加入不一致？

Question

在 MySQL 8 中，我们现在可以使用 JSON 类型的列，也可以使用内置函数，如 JSON_TABLE() 但由于我在不同的场景中使用，有时我会看到我没想到的结果。

Docs for JSON_TABLE() : https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

也许 JSON_TABLE 不是完成与一块 JSON 连接的方式。 MySQL 提供了一些搜索功能，但没有什么可以替代 JSON_TABLE()

Docs for JSON search functions: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

架构 (MySQL v8.0)

CREATE TABLE USER (
    NAME varchar(128) NOT NULL,
    METADATA JSON NULL
);

INSERT INTO USER VALUES
('John', '[1,3]'),
('Jane', '[2]'),
('Bob', null),
('Sally', '[9]');


CREATE TABLE ROLES (
  ID int NOT NULL,
  NAME varchar(64) NOT NULL
);

INSERT INTO ROLES VALUES
(1, 'Originator'),
(2, 'Approver'),
(3, 'Reviewer');

查询 #1 - 为什么鲍勃没有回来？

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

## Results ##
| NAME  | METADATA | ID  |
| ----- | -------- | --- |
| John  | [1, 3]   | 1   |
| John  | [1, 3]   | 3   |
| Jane  | [2]      | 2   |
| Sally | [9]      | 9   |

查询 #2

SELECT * FROM ROLES;

## Results ##
| ID  | NAME       |
| --- | ---------- |
| 1   | Originator |
| 2   | Approver   |
| 3   | Reviewer   |

查询 #3 - 为什么没有结果？

SELECT * 
FROM USER
JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

##There are no results to be displayed.

查询 #4 - 不使用 IN() JOIN 返回正确的结果。

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

## Results ##
| NAME | METADATA | ID  | ID  | NAME       |
| ---- | -------- | --- | --- | ---------- |
| John | [1, 3]   | 1   | 1   | Originator |
| John | [1, 3]   | 3   | 3   | Reviewer   |
| Jane | [2]      | 2   | 2   | Approver   |

查询 #5 - 鲍勃在哪里？

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
LEFT JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

## Results ##
| NAME  | METADATA | ID  | ID  | NAME       |
| ----- | -------- | --- | --- | ---------- |
| John  | [1, 3]   | 1   | 1   | Originator |
| Jane  | [2]      | 2   | 2   | Approver   |
| John  | [1, 3]   | 3   | 3   | Reviewer   |
| Sally | [9]      | 9   |     |            |

查询 #6 - 为什么当查询#3 什么都不返回时，带有 IN() 的 LEFT JOIN 会返回预期的结果？

SELECT * 
FROM USER
LEFT JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

## Results ##
| NAME  | METADATA | ID  | NAME       |
| ----- | -------- | --- | ---------- |
| John  | [1, 3]   | 1   | Originator |
| John  | [1, 3]   | 3   | Reviewer   |
| Jane  | [2]      | 2   | Approver   |
| Bob   |          |     |            |
| Sally | [9]      |     |            |

View on DB Fiddle

Answer 1

使用 ISNULL创建虚拟对象的属性 json

SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

DB FIDDLE

#1 No JOINS with JSON_TABLE where is Bob?
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

#2 Verify our ROLE recrods exist
SELECT * FROM ROLES;

#3 Regular JOIN with JSON_TABLE inside the IN(), why are there no results?
SELECT * 
FROM USER
JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM USER, JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

#4 Regular JOIN with JSON_TABLE, returns expected results
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

#5 LEFT JOIN with JSON_TABLE, where is Bob?
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
LEFT JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

#6 LEFT JOIN with JSON_TABLE inside the IN(), returns expected results
SELECT * 
FROM USER
LEFT JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);