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.
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.
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"] |
+--------------------------------------------------------+
|
0 comments:
Post a Comment