Как распарсить json строку в SQL запросе и использовать ее для сортировки?

Question

[NetyNicka]

Доброго времени суток товарищи! В очередной раз нужна ваша скорая помощь 8)
Есть sql запрос:

SELECT
	`resources`,
	`command`,
	`timestamp`
FROM
	`command`
WHERE
	(
		(
			(`user_id` = 1000196805)
			AND (
				`command`.`resources` LIKE '%\"14\":%'
			)
		)
		AND (`timestamp` >= 1439856000)
	)
AND (`timestamp` <= 1439942400)
ORDER BY
	`timestamp` DESC

Который выводит такой результат:

{"2":61,"14":"24"},	energize,	1439856776.5546
{"2":61,"14":24},	buy,	1439856776.0372
{"2":61,"14":25},	energize,	1439856775.2379

Вопрос, как сделать так, чтобы результат был бы такой:

{"2":61,"14":24},	energize,	1439856776.5546
{"2":61,"14":25},	energize,	1439856775.2379

(Строка {"2":61,"14":24}, buy, 1439856776.0372 удалена, т.к. "14":24 встречалось 2 раза)

Временное решение:

SELECT
DISTINCT
  `resources`,
  `command`,
  `timestamp`,
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(resources, ',', 2), ',' , -1 ), ':', -1), '"','') as dist
FROM
  `command`
WHERE
  (
    (
      (`user_id` = 1000196805)
      AND (
        `command`.`resources` LIKE '%\"14\":%'
      )
    )
    AND (`timestamp` >= 1439856000)
  )
AND (`timestamp` <= 1439942400)
GROUP BY dist
ORDER BY
  `timestamp` DESC

Answer 1

[Алексей Уколов]

DISTINCT здесь не поможет, нужно использовать GROUP BY.

SELECT
  `resources`,
  `command`,
  MAX(`timestamp`)
FROM
  `command`
WHERE
  (
    (
      (`user_id` = 1000196805)
      AND (
        `command`.`resources` LIKE '%\"14\":%'
      )
    )
    AND (`timestamp` >= 1439856000)
  )
AND (`timestamp` <= 1439942400)
ORDER BY
  `timestamp` DESC
GROUP BY
  `resources`

Вот только, что вернется в command в этом запросе, mysql не гарантирует - при группировке нужно в SELECT использовать только те поля, по которым производится группировка или аггрегатные функции (MAX, например, как в данном запросе).

Answer 2

[Evgeniy Zavyalov]

для начала объясните почему надо удалить
{"2":61,"14":24}, buy, 1439856776.0372
а не
{"2":61,"14":24}, energize, 1439856776.5546
т.к. запрос может вернуть в другой последовательности!?

Comments

[NetyNicka]

да по сути тут без разницы, какая строка будет удалена, главное, чтобы дублей не было.
временная затычка получилась такая:

REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(resources, ',', 2), ',' , -1 ), ':', -1), '"','') as dist

т.к. рядом с "14": могла оказаться строка ("14": "55") или число ("14": 55) в результате выводится отдельное поле, которое успешно сортируется и благодаря группировке и distinct имеет уникальное значение. Вполне возможно, что с помощью регулярных выражений это все можно упростить.