7

How can I sort a query based on the average Rating in a field in my table, the field itself is JSON text, structured like: 

[
 {"Type":1,"Rating":5},
 {"Type":2,"Rating":5},
 {"Type":3,"Rating":5}
]

I need my query to be sorted by the average of the 3 Ratings. There will always ever be only 3 values for this.

My current query is:

SELECT `Name`, `Town`, `Vehicle`, `Review`, `Rating`, `Pics`, `PostedOn` 
FROM `tbl_ShopReviews`
WHERE `Approved` = 1
ORDER BY `PostedOn` DESC

Current results:

Name    Town    Vehicle Review  Rating  Pics    PostedOn
Kevin   Chicopee    94 Corolla  Great stuff, very glad I brought it here    [{"Type":1,"Rating":5},{"Type":2,"Rating":5},{"Type":3,"Rating":5}]     \N
Improve this question
5
  • 2
    it's never a good idea to put json inside a table like this. have another column for rating and put ratings there Commented Dec 17, 2013 at 21:34
  • Notice how I have 3 different ratings types... should I have another column for that, and then create 3 records, 1 for each type. Because all 3 types are required Commented Dec 17, 2013 at 21:36
  • You should have a table for ratings and use a foreign key to reference this table. Read more on normalization Commented Dec 17, 2013 at 21:39
  • If you absolutely need JSON in your DB, consider using Postgres which can handle these requirements Commented Dec 17, 2013 at 21:42
  • i absolutley CANNOT change the structure of any table in the database without completely breaking the already built system. Nor can I obviously change db systems. Thus said... what about code.google.com/p/common-schema Commented Dec 18, 2013 at 4:01

7 Answers 7

Reset to default
26

Just for those like me, who googles and tries to find solution for laravel 5.4. You can use -> operator to extract JSON, that is equal to json_extract function or to column->"$.key" syntax. 

$users->orderBy('column->key', 'desc');

Looks like it would be very useful in late 2013 (smile).

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

4 Comments

You saved my day :)
This is the best and the Laravel way!
With the oldest SO sort, this answer is at the bottom but all other answers have a score of 0 or less... This really needs to be fixed and marked as the accepted answer
Note that this won't sort numerically, but alphanumerically ie "90" will come before "100" because it starts with a 9: db-fiddle.com/f/c247nMub1yHmyy8Yx5jQxa/1. This is because Laravel will add json_unquote before extracting the value. MariaDB will sort incorrectly even without json_unquote. For numeric sorting one could go $users->orderByRaw("cast(json_extract(column,'$.key') as unsigned) desc").
4

For example field_name has the value like

{"json_field_key1":"2016/11/24","json_field_key2":"value"}

Use this code for get the json field json_field_key1 based value in ORDER BY case

select table.*, SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) as json_field_name from table ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) DESC

If your value is in date format just modify this code like

order by DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1)) DESC

Improve this answer

2 Comments

Thank you. this is what i need and. but do you know how to convert it to laravel eloquent method? and i dont want to order by average. i want to order by the highest value
just use order by {json_field_name} ASC like select SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) as number_list from table order by number_list ASC || DESC
1

Would this feature from MySQL 5.7 help? http://blog.ulf-wendel.de/2013/mysql-5-7-sql-functions-for-json-udf/

Improve this answer

Comments

0

In my opinion the best solution is to be able to hook up update and insert events in your application for that reviews table and calculate the average in to another field.

Then those queries that need this info will be much easier to handle and will have a better performance.

Improve this answer

Comments

-1

The better solution is to parse the data before the insert, and have it ready for you in 3 columns or in 1 normalized column. Saying that, if you're dealing with a non-changeable situation, and have exactly 3 ratings always, you can try this

ORDER BY (substring(json, 21, 1)+
substring(json, 43, 1)+
substring(json,65, 1))/3 desc;

Please consider that this solution is the least maintainable and flexible of them all, and very bug prone. The real solution is restructuring your data.

Improve this answer

1 Comment

i am dealing with an unchangeable data structure... and yes... there will always b 3 rating types. I will try this in awhile
-1

the Rating field could be a table with user, type and vlaue as columns where the user is the key. Then you can just use mysql AVG() on the value column where the key match and then sort to that.

hope this help

Improve this answer

Comments

-2

There isn't an easy way to do this, in fact, I'm not even sure it's possible.

That being said, your database structure really shouldn't contain JSON if it's something you need access to in this respect, instead, why not add a Type field to your database?

Improve this answer

3 Comments

Notice how I have 3 different ratings types... should I have another column for that, and then create 3 records, 1 for each type. Because all 3 types are required
If you have three types then yes, you should have a field in your database to reflect this, whether you abstract it out to another table is entirely up to you, but you simply cannot order by the contents of a json string, nor should you ever attempt to do so, voting down won't change that fact.
i guess i should have stated in the q that yes...if i had the choice of channging the structure of the database then yes... this would be a possibilty... perhaps I need to stop assuming that others here will always only ever assume that everyone is a noob... downvote was firmly appropriate as it was not constructive to the context of the question at hand... maybe u should take a step back and deal with the question itself instead of simply stating yur opinion... not that yur opinion is a bad one... it does not deal with the problem at hand however

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.