如何统计SQL表中JSON数组字段内用户的出现次数？

现有如下结构的SQL表：

ID  | Need  | Fulfill_User
------------------------------------------------------------------------------
1   |   3   | ["A","A","B"]
2   |   4   | ["B","C","A","B"]
3   |   2   | ["C","A"]
4   |   1   | ["D"]

需要生成统计报表，计算Fulfill_User数组中各用户的总出现次数，得到结果：

• A = 4
• B = 3
• C = 2
• D = 1

不同数据库的实现方案 #

因为不同SQL数据库对JSON数组的解析语法不同，以下是主流数据库的实现代码：

1. PostgreSQL

利用jsonb_array_elements_text函数拆分JSON数组，再分组统计：

SELECT 
  user_name,
  COUNT(*) AS occurrence_count
FROM 
  your_table,
  jsonb_array_elements_text(Fulfill_User::jsonb) AS user_name
GROUP BY 
  user_name
ORDER BY 
  occurrence_count DESC;

2. MySQL 8.0+

使用JSON_TABLE将JSON数组转换为关系表结构后统计：

SELECT 
  user_name,
  COUNT(*) AS occurrence_count
FROM 
  your_table,
  JSON_TABLE(
    Fulfill_User,
    '$[*]' COLUMNS(user_name VARCHAR(50) PATH '$')
  ) AS users
GROUP BY 
  user_name
ORDER BY 
  occurrence_count DESC;

3. SQL Server

通过OPENJSON解析JSON数组，结合交叉应用实现统计：

SELECT 
  value AS user_name,
  COUNT(*) AS occurrence_count
FROM 
  your_table
CROSS APPLY 
  OPENJSON(Fulfill_User)
GROUP BY 
  value
ORDER BY 
  occurrence_count DESC;

内容的提问来源于stack exchange，提问作者Irvan Affandy