MySql 5.7 json_extract key
I have a table and it looks like this:
Table data
id params
1 {"company1X":{"price":"1124.55"},"company2X":{"price":"1,124.55"},"company3X":{"price":""},"company4X":{"price":""},"company5X":{"price":"1528.0"}}
I do not know the name of the "company" to use in my request.
How can I get the data sorted by price? Thank!
PS I tried to select json_extract (params, '$ [*]. Price') from the data, but it doesn't work (returns nulls).
source to share
$[*]
gets all the elements of a JSON array, not an object. It is an object, which is why you get NULL.
$.*
will give you all the elements in a JSON object, so $.*.price
will get you a JSON array of all prices.
mysql> select json_extract(params, '$.*.price') from foo;
+-------------------------------------------+
| json_extract(params, '$.*.price') |
+-------------------------------------------+
| ["1124.55", "1,124.55", "", "", "1528.0"] |
+-------------------------------------------+
Now there is a problem. As far as SQL is concerned, this is one line. It cannot be sorted with a regular order by
one that works with strings.
MySQL does not have a function to sort JSON ... so you are stuck. You can return a JSON array and let everyone who receives the data sort. You might be able to write a stored procedure to sort the array ... but that's a lot of work to support poor table design. Change the table instead.
The real problem is poor use of the JSON column.
JSON columns hit most of the SQL database (unlike PostgreSQL, which has much better JSON support). SQL databases work with rows and columns, but JSON runs what would be multiple rows and columns in a single cell.
For this reason, JSON columns should be used sparingly; usually when you don't know what data you need to store. Important information such as the "price" to be searched for and sorted should be done like regular columns.
You want to modify the table as a normal SQL table with columns for company name and price. Then you can use normal SQL functions like order by
, and the performance will be helpful when indexing. There is not enough information in your question to suggest what this table looks like.
source to share