I have a table with repeated Ids and with a JSON data that represents an alias "players_level", I'm trying to group the Ids so I don't have duplicated Ids but the problem is that when grouping the Ids I can't get the row with the greater "players_level" alias, example I have this data:

And I'm trying to group the data like this:

As you can see the row 2 disappeared because its players_level was 2 and the fourth one has level 3... but when trying to group using multiple techniques with SQL (Group By, Order By, Max...) I can't get the right row to output :(

Can anybody help me please? I have a fiddle link with the data example:
https://www.db-fiddle.com/f/sfQVa722kfkNw2JyRdQJZr/0

CodePudding user response：

You can use HAVING with a subselect with MAX

Schema (MySQL v5.7)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(6) unsigned NOT NULL,
  `score` int(6) unsigned NOT NULL,
  `players` varchar(200) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `score`, `players`) VALUES
  ('1', 0, '{}'),
  ('16', 1000, '{"1": {"222": [], "1232": [], "2824": [], "33332": [], "66345": []}}'),
  ('17', 0, '{"1": {}}'),
  ('16', 500, '{"1": {"2824": ["148500"]}, "48": {"2890": []}}')

Query #1

SELECT t.id, t.score, t.players,
  (CASE
  WHEN (JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '148500') = 1 OR JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '"148500"') = 1) THEN 3
  WHEN JSON_CONTAINS(JSON_KEYS(JSON_EXTRACT(t.players, '$."1"')), '"2824"') = 1 THEN 2
  WHEN JSON_CONTAINS(JSON_KEYS(t.players), '"1"') = 1 THEN 1
  ELSE 0
  END) players_level
from test t  
HAVING (id,players_level) IN (SELECT t.id,
  MAX(CASE
  WHEN (JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '148500') = 1 OR JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '"148500"') = 1) THEN 3
  WHEN JSON_CONTAINS(JSON_KEYS(JSON_EXTRACT(t.players, '$."1"')), '"2824"') = 1 THEN 2
  WHEN JSON_CONTAINS(JSON_KEYS(t.players), '"1"') = 1 THEN 1
  ELSE 0
  END) players_level
from test t
GROUP BY t.id);

View on DB Fiddle