This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous article, I have shared basic about MySQL 5.7 new JSON Data Type.
MySQL 5.7 also introduced different types of JSON related function.
In this post, I am sharing a few of those important JSON function.
First, create a sample table with JSON data type:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_TestJSON ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSON ,Address JSON ); |
Insert few valid JSON Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,'{"firstName": "Anvesh", "lastName": "Patel"}' ,' {"address" : { "India": "Hyderabad" ,"USA": "Newyork" } }' ) ,( 2 ,'Production' ,'{"firstName": "Neevan", "lastName": "Patel"}' ,' {"address" : { "India": "Ahmedabad" ,"USA": "Washington DC" } }' ) ,( 3 ,'Animation' ,'{"firstName": "Eric", "lastName": "Lorn"}' ,' {"address" : { "India": "Mumbai" ,"USA": "Chicago" } }' ); |
JSON_EXTRACT(): To extract or search value in JSON.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT JSON_EXTRACT(Address, "$.address.*") FROM tbl_TestJSON; +--------------------------------------+ | JSON_EXTRACT(Address, "$.address.*") | +--------------------------------------+ | ["Newyork", "Hyderabad"] | | ["Washington DC", "Ahmedabad"] | | ["Chicago", "Mumbai"] | +--------------------------------------+ SELECT JSON_EXTRACT(Address, "$.address.India") FROM tbl_TestJSON; +------------------------------------------+ | JSON_EXTRACT(Address, "$.address.India") | +------------------------------------------+ | "Hyderabad" | | "Ahmedabad" | | "Mumbai" | +------------------------------------------+ SELECT JSON_EXTRACT(Address, "$**.USA") FROM tbl_TestJSON; +----------------------------------+ | JSON_EXTRACT(Address, "$**.USA") | +----------------------------------+ | ["Newyork"] | | ["Washington DC"] | | ["Chicago"] | +----------------------------------+ |
JSON_SEARCH(): Use to search key value path.
one: The search terminates after the first match and returns one path string
all: The search returns all matching path strings such that no duplicate paths are included.
|
1 2 |
SELECT JSON_SEARCH(Address,'one','Mumbai') FROM tbl_TestJSON; SELECT JSON_SEARCH(Address,'all','Mumbai') FROM tbl_TestJSON; |
JSON_LENGTH():Use to check length of JSON document.
|
1 |
SELECT JSON_LENGTH (EmployeeDetails) FROM tbl_TestJSON; |
JSON_TYPE(): Use to check type of JSON document.
|
1 |
SELECT JSON_TYPE (EmployeeDetails) FROM tbl_TestJSON; |
JSON_KEYS(): Use to fetch list of JSON keys.
|
1 |
SELECT JSON_KEYS (EmployeeDetails) FROM tbl_TestJSON; |
JSON_MERGE(): Use to merge JSON data.
|
1 2 3 4 5 6 |
SELECT JSON_MERGE('["Anvesh", 1]', '{"Name": "Anvesh"}'); +---------------------------------------------------+ | JSON_MERGE('["Anvesh", 1]', '{"Name": "Anvesh"}') | +---------------------------------------------------+ | ["Anvesh", 1, {"Name": "Anvesh"}] | +---------------------------------------------------+ |
JSON_ARRAY_APPEND(): Use to append any data into JSON document.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]','$[1]','e'); +--------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]','$[1]','e') | +--------------------------------------------------------+ | ["a", ["b", "c", "e"], "d"] | +--------------------------------------------------------+ SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]','$[0]','g'); +--------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]','$[0]','g') | +--------------------------------------------------------+ | [["a", "g"], ["b", "c"], "d"] | +--------------------------------------------------------+ |
