SQL, how read out data and sort it in this way?

Question

I am sorry if this question is too noob.

I have a jason array stored in a column, and I wonder if possible to read one of array and sort it out.

Like say if this is the data in column A, {'class':'beatiful','name':'wonderful'}. I want to sort 'class' as ASC.

How can I implement sql sentence? May I have any example that I reference?

Or what kind of format of data stored can do this kind of request expect adding new column?

Thank you very much.

Perhaps you shouldn't be storing a JSON string in a RDBMS. Relational Databases work against if you just treat them like as a flat file. — N West, Feb 07 '13 at 19:40
@NWest do you have any ideas or alternative method can do this kind of request? I can change the format of data but I need them to be in one column — Micah, Feb 07 '13 at 19:43
@Till Why do they need to be in one column? I suggest: create a table with two columns, "class" and "name". Then you can `SELECT * FROM table ORDER BY class ASC` — showdev, Feb 07 '13 at 19:46
@showdev we are trying to create a adjustable database store different category of data into one table. Due to different category has different type of data. We can not crated new column for each category since in the same table. Do you have any ideas what we should look at to implement this? — Micah, Feb 07 '13 at 19:52
Is the field "class" common to all categories? If so, you can at least separate the "class" field to allow sorting. For example, your columns could be "class" and "data_array". (Aside: Beware, you are defeating the purpose of a database, as mentioned by N West). — showdev, Feb 07 '13 at 20:08
"An Adjustable Database Store"?? Tom Kyte talks about this. Your version is even worse than his since you are storing the entire data set in one string!! http://tkyte.blogspot.com/2009/01/this-should-be-fun-to-watch.html — N West, Feb 08 '13 at 14:39

score 1 · Answer 1 · answered Feb 07 '13 at 21:34

Doesn't JSON use double quotes?
Try some String functions to extract it. For example, you could find the position of "class". Then, look for the next opening double quoite, which would be the start of the value. Then, look for another, which would be the end of the value. Finally, get the sub-string out.

Pseudo code would look like this:
P1 = Position of class key = LOCATE('"class"', COL_A)
P2 = Position of open quote for value = LOCATE('"', COL_A, P1 + 7 )
P3 = Position of Close quote for value = LOCATE('"', COL_A, P2 + 1)
Substr that is the value = SUBSTRING(COL_A, P2, P3 - P2)

Expanding that:
SUBSTRING(COL_A, LOCATE('"', COL_A, LOCATE('"class"', COL_A)+ 7 ), LOCATE('"', COL_A, LOCATE('"', COL_A, P1 + 7 ) + 1) - LOCATE('"', COL_A, LOCATE('"class"', COL_A) + 7 ))

I have not run it on MySql to check, but you get the idea. Also, you might want to change it so that you can handle situations where the string "class" appears inside some other value field.

Once you have an expression that extracts the value, you can sort on it.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

It's not pretty, but this is basically splitting your JSON string and sorting by the class:

select *
  , substring_index(substring_index(
      replace(substr(A, 2, char_length(A) - 2), '\':\'', '\',\'')
      , ','
      , find_in_set('\'class\'', replace(substr(A, 2, char_length(A) - 2), '\':\'', '\',\'')) + 1
    ), ',', -1) as SortItem
from MyTable
order by SortItem;

Here is a demo SqlFiddle.

You might have to change delimiters according to quotes or apostrophes in your JSON.

Also, as the comments have pointed out, this is ugly because you're using your relational database in a non-relational manner.

edited May 23 '17 at 12:27

Community

1
1

answered Feb 07 '13 at 21:54

Tim Lehner

14,813
4
59
76

It's based upon `select substring_index(substring_index('a|bb|ccc|d', '|', 3), '|', -1);` which would return `ccc` – Tim Lehner Feb 07 '13 at 21:58
May I ask how much weight will gain to server to process these kind of search request assume there is 1000 rows?? – Micah Feb 07 '13 at 22:14
1000 is, generally speaking, not considered "big data" these days. Really, you must test performance on your server. – Tim Lehner Feb 07 '13 at 22:19

SQL, how read out data and sort it in this way?

2 Answers2