Extracting JSON value from a MySQL query

Question

I have data which is of type json in mysql and column name is 'student_data'. student_data:

{
  "STUDENT_HISTORY": [
    {
      "regno": "12345678",
      "UPDATE_DATE_UNIX_TIME": "65667"
    },
    {
      "regno": "12345332",
      "UPDATE_DATE_UNIX_TIME": "654333"
    }
  ]
}

I need to extract student_data based on condition where regno = "12345678" and here I have only regno value don't know index in json and I have tried json_extract query also which is given below.

SELECT JSON_EXTRACT(student_data,"$.STUDENT_HISTORY.regno") 
  FROM student table 
 WHERE STUDENT_HISTORY.regno = "12345678"

Answer 1

If you are running MySQL 8.0, you can use json_table():

select s.*, x.*
from student s
cross join json_table(
    s.student_date,
    '$.STUDENT_HISTORY' columns (
        regno varchar(50),
        UPDATE_DATE_UNIX_TIME varchar(50)
    )
) x
where x.regno = '12345678'

Answer 2

You still can use JSON_EXTRACT() with some little changes such as

SELECT *
  FROM student 
 WHERE JSON_UNQUOTE(JSON_EXTRACT(`student_data`,'$.STUDENT_HISTORY[0].regno'))='12345678'

Demo