dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Using JSON as a Data Type

MySQL is a very robust database that supports JSON as a datatype. See how to make this feature work for you.


advertisement

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.

 

Visit the DevX Tip Bank

 



   
Sridhar M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Thanks for your registration, follow us on our social networks to keep up-to-date