MySQL JSON_EXTRACT Examples

JSON_EXTRACT returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.

Consider the below dataset for practice:

DROP TABLE IF EXISTS tbSampleJSON;
CREATE TABLE tbSampleJSON(Id INT AUTO_INCREMENT PRIMARY KEY, jsonTxt JSON);

INSERT INTO tbSampleJSON (jsonTxt) VALUES(‘
{
“id”:”1″,
“data”:{
“empno”:1001
},
“task”:null,
“requestor”:{
“gender”:”Male”,
“status”:”SUBMITTED”,
“firstName”:”Shafi”,
“lastName”:”Shaik”,
“religion”:”Islam”,
“firstName”:”Shafi”,
“startDate”:”2022-10-01T00:00:00.000Z”,
“businessId”:7,
“empno”:1001,
“maritalStatus”:”Married”,
“salutation”:”Mr.”,
“visibility”:”VISIBLE”,
“accessLevel”:”ADMIN,MANAGER,HRBUSINESSPARTNER”,
“citizenship”:”Indian”,
“dateOfBirth”:”2020-02-20″,
“nationality”:”Indian”,
“placeOfBirth”:”Vijayawada”,
“workLocation”:”Dubai”,
“businessEmail”:”contact@bigdatansql.com”,
“businessPhone”:””,
“preferredName”:”Shafi”
},
“businessId”:7
}
‘);

Let’s extract the value from the JSON column using JSON_EXTRACT function.

SELECT Id,
JSON_EXTRACT(jsonTxt, ‘$.data.empno’) AS EmployeeNumber,
REPLACE(
CONCAT(JSON_EXTRACT(jsonTxt, ‘$.requestor.salutation’), ‘ ‘, JSON_EXTRACT(jsonTxt, ‘$.requestor.firstName’), ‘ ‘, JSON_EXTRACT(jsonTxt, ‘$.requestor.lastName’)), ‘”‘,”),
JSON_EXTRACT(jsonTxt, ‘$.requestor.maritalStatus’) AS maritalStatus,
JSON_EXTRACT(jsonTxt, ‘$.requestor.businessEmail’) AS EmailId,
JSON_EXTRACT(jsonTxt, ‘$.requestor.religion’) AS religion,
JSON_EXTRACT(jsonTxt, ‘$.requestor.placeOfBirth’) AS placeOfBirth
FROM tbSampleJSON;