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


Tip of the Day
Language: SQL
Expertise: Intermediate
Dec 12, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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.

Sridhar MS
 
Thanks for your registration, follow us on our social networks to keep up-to-date