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

Question

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;

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}]');

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.

Yet another example of JSON causing trouble that would be easily solved if you used normal tables and columns instead of JSON. — Bill Karwin
– Bill Karwin, Commented Apr 10, 2019 at 23:36
@dagalti yes I saw your fiddle but just wanted to be sure. How many tags can a post have? — Nick
– Nick, Commented Apr 10, 2019 at 23:37
@Nick a post can have 10-15 tags. users will select tags like stackoverflow. I want to show result specific tag based on tagged time order — dagalti
– dagalti, Commented Apr 10, 2019 at 23:39

Nick · Accepted Answer · 2019-04-11 00:17:04Z

3

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

edited Apr 11, 2019 at 0:17

answered Apr 10, 2019 at 23:58

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bill Karwin Over a year ago

I stand corrected, it is possible to do this. But it does seem like an uneconomical amount of work to use JSON, compared to how easy it would be to query a normalized set of tables for a many-to-many relationship.

Nick Over a year ago

@BillKarwin I totally agree (and had upvoted your comment to that effect on the question). It would be interesting to know if MySQL added JSON commands because people were already storing JSON or whether people started storing JSON because of the JSON commands. Either way a normalised set of tables would be much easier to deal with.

Bill Karwin Over a year ago

I assume the JSON features were added in response to the demands of their customers. Many customers would like to avoid learning to design a normalized database, or else they're put off by the inconvenience of ALTER TABLE. So they think JSON is the answer.

Chris Strickland Over a year ago

There are many cases where storing raw json is preferable, even with the problems entailed in querying that data back out, for instance in cases where the response is not stable, or where you know it will change over time, or where all the requirements aren't known at design time, or where the tradeoff in dev time (because all you have to do is store the raw response instead of deserializing) vs SQL overhead is acceptable. We ourselves do this in many cases, and I do know how to design a well structured table.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related