How to query JSON datatype in MySQL?

MySQLMySQLi Database

Use JSON data type from MySQL to work with JSON. Let us first create a table −

mysql> create table DemoTable
-> (
-> StudentName json
-> );
Query OK, 0 rows affected (0.74 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('["John", "Chris", "Robert","David","Sam"]');
Query OK, 1 row affected (0.33 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+---------------------------------------------+
| StudentName                                 |
+---------------------------------------------+
| ["John", "Chris", "Robert", "David", "Sam"] |
+---------------------------------------------+
1 row in set (0.00 sec)

Here is how you can query JSON data type −

mysql> SELECT *from DemoTable WHERE JSON_CONTAINS(StudentName, '["Chris"]');

Output

This will produce the following output −

+---------------------------------------------+
| StudentName                                 |
+---------------------------------------------+
| ["John", "Chris", "Robert", "David", "Sam"] |
+---------------------------------------------+
1 row in set (0.04 sec)
raja
Published on 10-Jul-2019 10:04:58
Advertisements