在MySQL 8中，我们现在可以使用JSON类型的列，还可以使用诸如JSON_TABLE（）的内置函数，但是由于我在不同的场景中使用，有时会看到意想不到的结果。

  

JSON_TABLE（）的文档：https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

也许JSON_TABLE并不是完成与JSON片段连接的方法。 MySQL提供了一些搜索功能，但是没有什么可以代替JSON_TABLE（）

  

用于JSON搜索功能的文档：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（）联接将返回正确的结果。

SELECT * 
FROM USER,"$[*]" 
      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,"$[*]" 
      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