Mysql Convert Json Data Type Column Into Rows

/0 Comments/in /by 

+---------+-----+----------------------------------------+
| user_id | roles                                        |
+---------+-----+----------------------------------------+
|       1 | ["Admin","Editor","Developer","Programmer"]  |
|       2 | ["Admin","Editor","Developer"]               |
+---------+-----+----------------------------------------+

SELECT
  user_id,
  idx,
  JSON_EXTRACT(roles, CONCAT('$[', idx, ']')) AS roles
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  SELECT  3 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  4
  ) AS indexes
WHERE JSON_EXTRACT(roles, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY user_id, idx;

+---------+-----+-------------+
| user_id | idx | roles       |
+---------+-----+-------------+
|       1 |   0 | Admin       |
|       1 |   1 | Editor      |
|       1 |   2 | Developer   |
|       1 |   3 | Programmer  |
|       2 |   0 | Admin       |
|       2 |   1 | Editor      |
|       2 |   2 | Developer   |
+---------+-----+-------------+

mysqladmin to kill the runaway query

/0 Comments/in , /by

Run the following commands:

mysqladmin -uusername -ppassword pr

Then note down the process id.

mysqladmin -uusername -ppassword kill pid

Do Not ORDER BY RAND()

/0 Comments/in /by

If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.

// what NOT to do:
$r = mysql_query(“SELECT username FROM user ORDER BY RAND() LIMIT 1”);

// much better:

$r = mysql_query(“SELECT count(*) FROM user”);
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] – 1);

$r = mysql_query(“SELECT username FROM user LIMIT $rand, 1”);

So you pick a random number less than the number of results and use that as the offset in your LIMIT clause.