MySQL - Order elements by JSON value in array?

Question

Hey I've been searching and looking all day. Have gotten further into understanding my problem and the options. By I am at a lack of understanding.

My general issue is that I am trying to SELECT rows based on values in the JSON and order the selection by there selected values.

I have a table (elements), with two columns: Person and Tags. Tags contains a JSON array that can have multiple JSON objects inside it. Objects always have "name" and "sort".

    +-------------+------------------------------------------------------------------+
    | Person      | tags                                                       
    +-------------+------------------------------------------------------------------+
    | William     | [{"name": "apple", "sort": "1"}, {"name": "orange", "sort": "2"}]      
    | Anna        | [{"name": "apple", "sort": "3"}, {"name": "orange", "sort": "1"}]       
    | Michael     | [{"name": "apple", "sort": "2"}]                                     
    +-------------+------------------------------------------------------------------+

Ideally what I would like to do is tell the database using to SELECT * FROM elements WHERE tags (has an object where name is "apple") ORDER by (The sort value from the object where it matched);

So if I gave it "apple" it would list: William, Michael, Anna.

If I gave it "orange" it would list: Anna, William.

I have been looking into having SELECTs within SELECTs (Subqueries) but I can find the right combination with JSON. Below is the closest I have gotten, but I can tell it needs something more advanced?

SELECT * 
FROM elements 
WHERE JSON_SEARCH( tags, 'one', 'apple', NULL, '$[*].name' ) IS NOT NULL

This will return all the people, with apple tag, but it will not order them based on the sort.

Any suggestions are welcome, thanks in advance.

Bernd Buffen · Accepted Answer · 2020-01-24 10:16:05Z

Here a "nice" Query that works for you. You only must change res.* to res.name for your query.

SELECT res.* FROM (
    SELECT 
        SUBSTRING_INDEX( JSON_UNQUOTE (JSON_SEARCH(e.tags, 'one', 'apple')),'.',1) as idx
        ,e.* FROM `elements` AS e ) AS res
 WHERE res.idx IS NOT NULL
 ORDER BY JSON_UNQUOTE(JSON_EXTRACT(res.tags,CONCAT(res.idx,'.sort')));

SAMPLE

mysql> select * from elements;
+----+---------+-------------------------------------------------------------------+
| id | Person  | tags                                                              |
+----+---------+-------------------------------------------------------------------+
|  1 | William | [{"name": "apple", "sort": "1"}, {"name": "orange", "sort": "2"}] |
|  2 | Anna    | [{"name": "apple", "sort": "3"}, {"name": "orange", "sort": "1"}] |
|  3 | Michael | [{"name": "apple", "sort": "2"}]                                  |
+----+---------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Find apple

    mysql> SELECT res.* FROM (
        -> SELECT 
        -> SUBSTRING_INDEX( JSON_UNQUOTE (JSON_SEARCH(e.tags, 'one', 'orange')),'.',1) as idx
        -> ,e.* FROM `elements` AS e ) AS res
        ->  WHERE res.idx IS NOT NULL
        ->  ORDER BY JSON_UNQUOTE(JSON_EXTRACT(res.tags,CONCAT(res.idx,'.sort')));
+----+---------+-------------------------------------------------------------------+
| id | Person  | tags                                                              |
+----+---------+-------------------------------------------------------------------+
|  1 | William | [{"name": "apple", "sort": "1"}, {"name": "orange", "sort": "2"}] |
|  2 | Anna    | [{"name": "apple", "sort": "3"}, {"name": "orange", "sort": "1"}] |
|  3 | Michael | [{"name": "apple", "sort": "2"}]                                  |
+----+---------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Find orange

mysql> SELECT res.* FROM (
    -> SELECT 
    -> SUBSTRING_INDEX( JSON_UNQUOTE (JSON_SEARCH(e.tags, 'one', 'orange')),'.',1) as idx
    -> ,e.* FROM `elements` AS e ) AS res
    ->  WHERE res.idx IS NOT NULL
    ->  ORDER BY JSON_UNQUOTE(JSON_EXTRACT(res.tags,CONCAT(res.idx,'.sort')));
+------+----+---------+-------------------------------------------------------------------+
| idx  | id | Person  | tags                                                              |
+------+----+---------+-------------------------------------------------------------------+
| $[1] |  2 | Anna    | [{"name": "apple", "sort": "3"}, {"name": "orange", "sort": "1"}] |
| $[1] |  1 | William | [{"name": "apple", "sort": "1"}, {"name": "orange", "sort": "2"}] |
+------+----+---------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

j-petty · Accepted Answer · 2020-01-23 06:07:20Z

0

There's a similar question here which might assist you.

You may be able to use JSON_EXTRACT however it gets complicated because you're storing an array of JSON data so you might also require a sub query.

I'll propose an alternate solution though. Have you considered restructuring your database across multiple tables instead of using a JSON blob?

You could have a table dedicated to storing the tags, something like this:

+-------------+------------------------------------------------------------------+
| tagid       | name                                                       
+-------------+------------------------------------------------------------------+
| 1           | apple     
| 2           | orange       
| 3           | banana                                   
+-------------+------------------------------------------------------------------+

Another table for storing the people:

+-------------+------------------------------------------------------------------+
| id          | name                                                       
+-------------+------------------------------------------------------------------+
| 1           | William      
| 2           | Anna      
| 3           | Michael                                     
+-------------+------------------------------------------------------------------+

And finally a table for storing the many-many relationship between people and tags. This is also where you can store the sort order:

+-------------+--------------+-----------+--------------------------------------+
| id          | personid     | tagid     | sort                                          
+-------------+--------------+-----------+--------------------------------------+
| 1           | 1            | 1         | 1
| 2           | 1            | 2         | 2
| 3           | 2            | 1         | 3  
| 4           | 2            | 2         | 1
| 5           | 3            | 1         | 1                             
+-------------+--------------+-----------+---------------------------------------+

A database model that looks more like this will make complicated queries much simpler and won't require complex subqueries, just joins. It's likely to improve your ability to report on data relating to your tags, if that's something that's important to you.

answered Jan 23, 2020 at 6:07

j-petty

3,1161 gold badge16 silver badges23 bronze badges

2 Comments

William Larsen Bang Over a year ago

This is not a possibility for me, In reality I have multiple of these JSONs archived in different columns, and would have to create many tables for many relationships, which would be much more combersome I think. Thanks for reply though.

j-petty Over a year ago

Sorry to hear that this isn't a suitable solution for you. Another option might be to perform the sort in your application (e.g. API) rather than as part of the query?

Collectives™ on Stack Overflow

MySQL - Order elements by JSON value in array?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related