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"

Format your question - it is unreadable now. Provide sample data as CREATE TABLE + INSERT INTO scripts. Show desired output in textial table-formatted form. Specify precise MySQL version. — Akina, Nov 25 '20 at 11:45

score 1 · Answer 1 · answered Nov 25 '20 at 11:45

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'

answered Nov 25 '20 at 11:45

GMB

216,147
25
84
135

Getting syntax error 'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',UPDATE_DATE_UNIX_TIME varchar(50))) x where x.regno ='12345678' at line 1 ' – Neela Kandan Nov 26 '20 at 07:08

Barbaros Özhan · Answer 2 · 2020-11-25T11:57:07.587

0

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

edited Nov 25 '20 at 11:57

answered Nov 25 '20 at 11:51

Barbaros Özhan

59,113
10
31
55

we need a query which is not based on index. it should be generalized. regno = ' 12345678' only we knew this value. we don't know index in json. – Neela Kandan Nov 26 '20 at 07:10
@NeelaKandan well, you should explain the logic in the question explicitly then – Barbaros Özhan Nov 26 '20 at 08:09
yes, most probably if have added another json in my question, understanding would be better let me edit it. – Neela Kandan Nov 26 '20 at 09:07

Extracting JSON value from a MySQL query

2 Answers2