mysql json中按键统计数据

{
  "name": "Alice",
  "age": 25,
  "city": "New York"
}

SELECT JSON_KEYS('{
  "name": "Alice",
  "age": 25,
  "city": "New York"
}');

["name", "age", "city"]

["apple", "banana", "orange"]

SELECT JSON_LENGTH('["apple", "banana", "orange"]');

3

[
  {"name": "Alice", "age": 25},
  {"name": "Bob", "age": 30},
  {"name": "Alice", "age": 28},
  {"name": "Charlie", "age": 35}
]

SELECT key, COUNT(*) AS count
FROM (
  SELECT JSON_KEYS(json_data) AS keys
  FROM table_name
) AS t,
JSON_TABLE(t.keys, '$[*]' COLUMNS (
  key VARCHAR(255) PATH '$'
)) AS k
GROUP BY key;

+------+-------+
| key  | count |
+------+-------+
| name |   4   |
| age  |   4   |
+------+-------+