devxlogo

Using JSON as a Data Type

Using JSON as a Data Type

MySQL is a very robust database that supports JSON as a data type. This will be very useful for applications that need the JSON data type for certain types of processing.

Consider the following example, here, we will create, populate and query the JSON data, which should clarify its usage.

Queries:

For table creation:

CREATE TABLE 'EMP_DETAILS' (	'ID' INT(5) NOT NULL,	'FIRSTNAME' VARCHAR(25),	'LASTNAME' VARCHAR(25),	'QUALIFICATION' JSON,	PRIMARY KEY ('ID')) 

For data insertion in table

INSERT INTO EMP_DETAILS VALUES (1, 'John', 'win', '{"masters": "Algorithms", "bachelors": "Computer Science"}');INSERT INTO EMP_DETAILS VALUES (2, 'Brian', 'Lee', '{"masters": "Data Science", "bacherlors": "Computer Science"}');INSERT INTO EMP_DETAILS VALUES (3, 'Steve', 'wall', '{"masters": "Optimization", "bacherlors": "Computer Science"}'); 

And, we now want to extract the values in the masters field of the JSON object for all the rows, we have the query as defined below.

SELECT ID, QUALIFICATION ->> '$.masters' QUALIFICATION FROM EMP_DETAILS

Output:

+---------------------+| ID  | QUALIFICATION |+---------------------+|  1  | Algorithms    ||  2  | Data Science  ||  3  | Optimization  |+---------------------+

Please observe the ->>, which extracts only the text that is intended.

devx-admin

Share the Post: