'Get last element from a item in a JSON column in SQL

How I get last element from a item in a JSON column in SQL?

I have:

| id | log_json |
| 1        | {'model':'n1n', 'serial':'serial1', 'msg':'last_msg_serial1'}            |
| 2        | {'model':'n1n', 'serial':'serial1', 'msg':'second_msg_serial1'}  |
| 3        | {'model':'n1n', 'serial':'serial1', 'msg':'first_msg_serial1'}  |
| 4        | {'model':'z2z', 'serial':'serial2', 'msg':'last_msg_serial2'}  |
| 5        | {'model':'f3f', 'serial':'serial3', 'msg':'last_msg_serial3'}  |
| 6        | {'model':'f3f', 'serial':'serial3', 'msg':'first_msg_serial3'}  |

I need only the last item for each 'serial'. Like:

| id_log | model | serial | msg |
| 1        | 'n1n' | 'serial1' | 'last_msg_serial1'      |
| 4        | 'z2z' | 'serial2' | 'last_msg_serial2'     |
| 5        | 'f3f' | 'serial3' | 'last_msg_serial3'     |

I'm trying:

SELECT
  id as id_log,
  JSON_UNQUOTE(log_json->"$.model") as model,
  JSON_UNQUOTE(log_json->"$.serial") as serial,
  JSON_UNQUOTE(log_json->"$.msg") as msg
FROM table_mysql, (
  SELECT max(id) as last_id
  FROM table_mysql GROUP BY JSON_UNQUOTE(log_json->"$.serial")
  ) t1
WHERE id = t1.last_id
GROUP BY JSON_UNQUOTE(log_json ->"$.serial")
mysqljson


Solution 1:[1]

You could use ROW_NUMBER, but your query is almost good:

SELECT
  id as id_log,
  JSON_UNQUOTE(log_json->"$.model") as model,
  JSON_UNQUOTE(log_json->"$.serial") as serial,
  JSON_UNQUOTE(log_json->"$.msg") as msg
 FROM table_mysql
 JOIN (
      SELECT max(id) as last_id
      FROM table_mysql GROUP BY JSON_UNQUOTE(log_json->"$.serial")
      ) as t1
    ON id = t1.last_id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions