-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathJSON.sql
144 lines (112 loc) · 3.14 KB
/
JSON.sql
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
##########################
# 16.11.2017 by [email protected]
# JSON on MySQL 5.7.X
############
SET @document = '[10,20,[30,40]]';
select json_extract(json_extract(@document,'$[2]'),'$[1]');
CREATE TABLE features (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
feature JSON NOT NULL
);
INSERT into features (feature) VALUES ('{
"type":"Feature",
"geometry":{
"type":"Polygon",
"coordinates":[
[
[-122.42200352825247,37.80848009696725,0],
[-122.42207601332528,37.808835019815085,0],
[-122.42110217434865,37.808803534992904,0],
[-122.42106256906727,37.80860105681814,0],
[-122.42200352825247,37.80848009696725,0]
]
]
},
"properties":{
"TO_ST":"0",
"BLKLOT":"0001001",
"STREET":"UNKNOWN",
"FROM_ST":"0",
"LOT_NUM":"001",
"ST_TYPE":null,
"ODD_EVEN":"E",
"BLOCK_NUM":"0001",
"MAPBLKLOT":"0001001"
}
}');
INSERT into features (feature) VALUES ('{
"type":"Feature",
"geometry":{
"type":"Polygon",
"coordinates":[
[
[-122.42200352825247,37.80848009696725,0],
[-122.42207601332528,37.808835019815085,0],
[-122.42110217434865,37.808803534992904,0],
[-122.42106256906727,37.80860105681814,0],
[-122.42200352825247,37.80848009696725,0]
]
]
},
"properties":{
"TO_ST":"0",
"BLKLOT":"0001001",
"STREET":"MARKET",
"FROM_ST":"0",
"LOT_NUM":"1001",
"ST_TYPE":null,
"ODD_EVEN":"E",
"BLOCK_NUM":"0001",
"MAPBLKLOT":"0001001"
}
}');
SELECT json_extract(feature,'$.geometry.coordinates') FROM features
WHERE feature->"$.properties.STREET" = 'MARKET';
SELECT feature -> "$.geometry.coordinates" FROM features
WHERE feature->"$.properties.STREET" = 'MARKET';
##
## install mysql shell
# sudo yum install mysql-shell
## enable x protocol
#mysqlsh -u root -h localhost --classic --dba enableXProtocol
## in 8.x
## in the log
#2017-10-15T18:52:51.558629Z 0 [Note] Plugin mysqlx reported: 'X Plugin listens on TCP (bind-address:'::', port:33060)'
#2017-10-15T18:52:51.558653Z 0 [Note] Plugin mysqlx reported: 'X Plugin listens on UNIX socket (/var/run/mysqld/mysqlx.sock)'
#2017-10-15T18:52:51.558680Z 0 [Note] Plugin mysqlx reported: 'Server starts handling incoming connections'
##CRUD
#mysqlsh --uri root@localhost
./mysqlsh -mc -u root -poracle -h mysqlserver01
db = session.getSchema('world_x')
db
db.getCollections()
\use world_x
# insert
db.countryinfo.add(
{
GNP: .6,
IndepYear: 1948,
Name: "Acme",
_id: "ACM",
demographics: {
LifeExpectancy: 120,
Population: 22
},
geography: {
Continent: "Asia",
Region: "British Islands",
SurfaceArea: 193
},
government: {
GovernmentForm: "Monarchy",
HeadOfState: "Michael Bates"
}
}
);
## remove
db.countryinfo.remove("_id='ACM'");
db.getCollection("CountryInfo").find('Name= "United States"').limit(1)
## from sql
\sql
SELECT doc FROM world_x.CountryInfo WHERE (JSON_EXTRACT(doc,'$.Name') = 'United States') LIMIT 1;
##