如何从存储在SQL中一列中的json中获取前10个值

Question

我有一个表名players，其中我将json数组存储到stats列中，这就像下面的

{"sbuilders":{"perfect_builds":2,"rounds_wins":3,"games_played":10},
"ffa":{"uhc":{"deaths":12,"kills":8},
"op":{"kills":5,"deaths":1},
"classic":{"deaths":70,"kills":115}}}

但是，如何从该表中获取排名前十的sbuilder game_played？

Answer 1

我在mysql中很少使用JSON，所以这很有趣。

您可以使用JSON_EXTRACT从JSON对象中提取JSON属性；然后你可以随心所欲地使用这个结果。

select JSON_EXTRACT(i.j, '$.sbuilders.games_played') as gamesplayed from i;

https://www.db-fiddle.com/f/mNiqfif4si7BMZG4vA5cvJ/0

Answer 2

似乎你需要在

SELECT *
FROM players
ORDER BY JSON_EXTRACT(stats, '$.sbuilders.games_played') DESC
LIMIT 10