SQL加入JSON对象中的每个ID

Question

我有一个json列，其中包含另一个表的col_values。我想从json对象中的每个项目中返回其他表的行。

如果这是int列，我将使用JOIN，但是我需要JOIN JSON中的每个条目目的。

拍摄：

作者 ：

| id | name | projects (JSON)   |   
|:-- |:-----|:------------------|
| 1  | Andy | ["1","2","3","4"] |
| 2  | Hank | ["3","4","5","6"] |
| 3  | Alex | ["1","7","8","9"] |
| 4  | Joe  | ["1","5","6","7"] |
| 5  | Ken  | ["2","4","5","6"] |
| 6  | Zach | ["2","7","8","9"] |
| 7  | Walt | ["2","5","6","7"] |
| 8  | Mike | ["2","3","4","5"] |

Cities ：

| id | name     | project |
|:-- |:---------|:--------|
| 1  | Boston   | 1       |
| 2  | Chicago  | 2       |
| 3  | Cisco    | 3       |
| 4  | Seattle  | 4       |
| 5  | North    | 5       |
| 6  | West     | 6       |
| 7  | Miami    | 7       |
| 8  | York     | 8       |
| 9  | Tainan   | 9       |
| 10 | Seoul    | 1       |
| 11 | South    | 2       |
| 12 | Tokyo    | 3       |
| 13 | Carlisle | 4       |
| 14 | Fugging  | 5       |
| 15 | Turkey   | 6       |
| 16 | Paris    | 7       |
| 17 | Midguard | 8       |
| 18 | Fugging  | 9       |
| 19 | Madrid   | 1       |
| 20 | Salvador | 2       |
| 21 | Everett  | 3       |

我需要 NAME for Mike的每个城市（ID = <代码> 8 ）。

所需的结果：

这是我得到的，我需要得到的（按名称订单<代码>订单）。

输出：

| id | name     | project |
|:---|:---------|:--------|
| 13 | Carlisle | 4       |
| 2  | Chicago  | 2       |
| 3  | Cisco    | 3       |
| 21 | Everett  | 3       |
| 14 | Fugging  | 5       |
| 5  | North    | 5       |
| 20 | Salvador | 2       |
| 4  | Seattle  | 4       |
| 11 | South    | 2       |
| 12 | Tokyo    | 3       |      

当前查询，但这不是最好的方法...

sql ＆gt;

SELECT c.* 
  FROM cities c 
  WHERE EXISTS (
    SELECT 1 
      FROM writers w 
      WHERE JSON_CONTAINS(
        w.projects, CONCAT('\"', c.project, '\"')) 
      AND w.id = '8'
  ) 
 ORDER BY c.name;

---

背景

如果它很重要，我需要继续使用json作为数据类型，因为使用此数据库的服务器端软件通常会读取该列，如果以JSON对象的形式表示该列。

通常，我只需在我的服务器端语言中进行几个数据库调用并通过该JSON对象迭代，但是对于许多数据库调用而言，这太昂贵了，尽管进行多个数据库呼叫的分页呼叫更加昂贵。

我需要单个数据库调用中的所有结果。因此，我需要加入，或以其他方式循环通过SQL中的JSON对象中的每个项目。

Answer 1

从开始

per a 来自用户，有一种更好的方法...

sql ＆gt;

SELECT c.*
  FROM writers w
  JOIN cities c ON JSON_CONTAINS(w.projects, CONCAT('\"', c.project, '\"'))
  WHERE w.id = '8'
  ORDER BY c.name;

输出相同...

输出：

ID	名称	项目
13	Carlisle	4
2	芝加哥	2
3	Cisco	3
Cisco 3 21	Everett	3
14	Fugging	5
5 5	North	5
20	Salvador	2
4	Seattle	4 Seattle 4
Seattle 4 11	South	2
South 2	Tokyo	3

< strong>