MySQL JSON Data Need SQL Query to Sort Array of Objects Order based on key value in object

Question

1. I have a table named posts with columns id, title, tags.

    CREATE TABLE IF NOT EXISTS `posts` (
      `id` int(6) unsigned NOT NULL,
      `title` varchar(100) NOT NULL,
      `tags` json NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
   

2. Stores data like [{"tag": "android", "time": 122}, {"tag": "apple", "time": 140}] in tags field.

     INSERT INTO `posts` (`id`, `title`, `tags`) VALUES
          ('1', 'First Post', '[{"tag": "andoroid", "time": 123}, {"tag": "mobiles", "time": 432} ]'),
          ('2', 'Second Post', '[{"tag": "apple", "time": 125}]'),
          ('3', 'Third Post', '[{"tag": "android", "time": 124}]'),
          ('4', 'Fourth Post', '[{"tag": "mobiles", "time": 472}, {"tag": "android", "time": 129}]'),
          ('5', 'Fifth Post', '[{"tag": "android", "time": 122}, {"tag": "apple", "time": 140}]'),
          ('6', 'Sixth Post', '[{"tag": "mobiles", "time": 121}, {"tag": "apple", "time": 120}]'),
          ('7', 'Seventh  Post', '[{"tag": "apple", "time": 120}, {"tag": "mobiles", "time": 130}]'),
          ('8', 'Eigth  Post', '[{"tag": "android", "time": 126}]'),
          ('9', 'Nineth  Post', '[{"tag": "mobiles", "time":132}]');
   

3. Filtering data from table based on tag value for eg. tag == "android" . To achieve that I am using mysql query

   SELECT id, title, tags FROM posts where JSON_CONTAINS(
                    tags , '{"tag": "android"}'
                    ) ;
   

It works fine. DB Fiddle : https://www.db-fiddle.com/f/5Hw1FyL3Qv2RLtCw4tZbyh/1

Further I need to order result based on time value in tags . tag == 'android' and order by time .

Thanks in advance for your kind help.

Answer 1

The only way to solve this in MySQL versions before 8 is to use a stored function to find the minimum time value from each set of tags. Something like this:

DELIMITER //
CREATE FUNCTION min_time(tags JSON)
RETURNS INT
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE mint INT DEFAULT 9999;
  DECLARE thist INT;
  SET thist = JSON_EXTRACT(tags, CONCAT('$[', i, '].time'));
  WHILE thist IS NOT NULL DO
    IF thist < mint THEN
      SET mint = thist;
    END IF;
    SET i = i + 1;
    SET thist = JSON_EXTRACT(tags, CONCAT('$[', i, '].time'));
  END WHILE;
  RETURN mint;
END //

Then you can use a query like this:

SELECT id, title, tags, min_time(tags) AS min_time
FROM posts 
WHERE JSON_CONTAINS(tags , '{"tag": "android"}')
ORDER BY min_time

Output:

id  title       tags                                                                tags->'$[*].time'    min_time
4   Fourth Post [{"tag": "mobiles", "time": 472}, {"tag": "android", "time": 121}]  [472, 121]          121
5   Fifth Post  [{"tag": "android", "time": 122}, {"tag": "apple", "time": 140}]    [122, 140]          122
3   Third Post  [{"tag": "android", "time": 124}]                                   [124]               124
8   Eigth Post  [{"tag": "android", "time": 126}]                                   [126]               126

Demo on dbfiddle

This may be over-engineered as it sorts by the minimum time of any tag on the post. If you only want to sort by the time associated with the android tag (the one you are searching on), you can use this simplified query:

SELECT id, title, tags,
       JSON_EXTRACT(tags, CONCAT(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(tags, 'one', 'android')), '.', 1), '.time')) AS tag_time
FROM posts 
WHERE JSON_CONTAINS(tags , '{"tag": "android"}')
ORDER BY tag_time

Output:

id  title       tags                                                                tag_time
3   Third Post  [{"tag": "android", "time": 75}]                                    75
4   Fourth Post [{"tag": "mobiles", "time": 472}, {"tag": "android", "time": 121}]  121
5   Fifth Post  [{"tag": "android", "time": 122}, {"tag": "apple", "time": 140}]    122
8   Eigth Post  [{"tag": "android", "time": 126}]                                   126

Demo on dbfiddle