Please note that index links point to page beginnings from the print edition. Locations are approximate in e-readers, and you may need to page down one or more times after clicking a link to get to the indexed material.
: (colon), 2
, (comma), 2
* (wildcards), 9, 18, 27, 46–47
* (asterisk), 9, 18, 27, 46–47
. (period), 14
$ (dollar sign), 17
'' (double single quote), 73, 74
: (colon), 89
{ } (curly brackets), 2, 3, 26, 73
$ character, 14
* wildcard, 27
** wildcard, 27
[ ] (square brackets), 2, 3, 26
-> operator, 100
->> operator, 34–35
A
ALTER TABLE statement, 56–58
apostrophe ('), 73–74
arguments, 71–72
arrayAppend, 85
arrays
adding, 84
associative, 64
cells, 14
creating from non-JSON data, 45–46
inserting into, 28–29
length of, 39
nested, 39
vs. objects, 26
overview, 2–3
using, 26
values, 26
AS keyword, 54
asterisk (*), 9, 18, 27, 46–47
AUTO_INCREMENT attribute, 54
B
BASH scripts, 69
bind() function, 89–90
binding, 89–90
Boolean data, 50
C
C++ language, 94
casting, 46–47
cells, 14
CLI (Command Line Interface), 78
collections, 81–83
considerations, 78
dropping, 92
indexing, 90–92
overview, 81–82
removing, 92
restaurant collection, 73–74
vs. tables, 99–100
comma (,), 2
Command Line Interface (CLI), 78
comma-separated values (CSV), 71
connections, 79, 80–81, 96, 97
CRUD (Create, Replace, Update, and Delete), 80, 83–86, 99
CSV (comma-separated values), 71
curly brackets { }, 2, 3, 26, 73
D
data, 25–41. See also JSON data; non-JSON data
adding new data, 30–31
benefits of JSON-encloded data, 1–2
Boolean, 50
converting to relational tables, 48–49
creating from non-JSON data, 44
deleting from documents, 32–33
dropping/removing, 92
extracting from tables, 100
finding/getting, 19–23
impotency, 8
improving readability of, 15–16
inserting, 33–34
lifespan, 92
loading, 67–75
mapping to temporary relational tables, 48
merging, 35–38
missing, 49–50
nested, 50–51
non-JSON. See non-JSON data
removing, 29–30
replacing in documents, 31–32
spatial, 59
stored as strings, 8–10
stored in text fields, 8
validating, 70–71
data types. See also JSON data type
arrays/objects and, 37
casting, 46–47
determining, 39
geometric/geographic, 59
databases
creating with JSON data type, 26
document. See MySQL Document Store
example, 4–6
MySQL, 71
world, 4
Developer Milestone Release, 48
doc column, 78
document database. See MySQL Document Store
documents
adding arrays to, 84
adding key/value pairs, 84
appending arrays to, 85
casting as tables, 100
collections, 81–83
deleting data from, 32–33
determining storage size of, 40
inserting data into, 33–34
inserting values into, 30–31
length of, 38–39
maximum depth, 38
merging, 35–38
nested data, 50–51
removing, 86
removing key/value pairs, 85
reporting freed storage space, 41
sorting, 88–89
updating/replacing data in, 33–34
updating/replacing values in, 31–32
validating, 40
dollar sign ($), 17
double single quote (''), 73, 74
dropping collections, 92
E
ECMA (European Computer Manufacturers Association) Standard 404, 2
ECMAScript Language Specification, 2
European Computer Manufacturers Association. See ECMA entries
example database, 4–6
EXPLAIN command, 91
F
fields
selecting, 72–73
sorting, 88–89
fields() function, 88–89
FOR ORDINALITY operator, 51
foreign-key constraints, 54
full table scan, 90
G
generated columns, 53–59
geographic information system (GIS), 59
geography key, 17
GeoJSON, 59–62
geometries, 59–62
getCollections() function, 81–82
getTables() function, 99
GIS (geographic information system), 59
Google Protocol Buffers, 103–104
I
_id column, 78
_id values, 92
IETF RFC 7159, 2
impotency (data), 8
indexes, 90–92
collections, 90–92
creating, 90–92
generated columns and, 53, 56–58
speeding searches with, 10
unique/nonunique, 90–92
InnoDB tables, 73
INTEGER value, 47
Internet Engineering Task Force. See IETF
J
Java, 94
JavaScript interpreter, 78, 98
JavaScript Object Notation. See JSON entries
jq (JSON CLI parser), 71–73, 104
JSON (JavaScript Object Notation)
arrays, 2–3
attributes, 2
benefits of, 1–2
GeoJSON, 59–62
grammar, 2
overview, 2–3
standards, 2
JSON_ BIGINT_AS_STRING option, 64
JSON CLI parser (jq), 71–73, 104
JSON data type. See also data types
casting data as, 46–47
considerations, 7, 8, 10–11, 77
creating databases with, 26
creating tables with, 26
using, 10–11
JSON datasets, 16, 49, 67–75, 104
JSON documents
adding arrays to, 84
adding key/value pairs, 84
appending arrays to, 85
casting as tables, 100
collections, 81–83
deleting data from, 32–33
determining storage size of, 40
inserting data into, 33–34
inserting values into, 30–31
length of, 38–39
maximum depth, 38
merging, 35–38
nested data, 50–51
removing, 86
removing key/value pairs, 85
reporting freed storage space, 41
sorting, 88–89
updating/replacing data in, 33–34
updating/replacing values in, 31–32
validating, 40
JSON MERGE functions, 35–38
JSON processor, 71–73
JSON string data, 8–10
JSON strings
converting to PHP variables, 64–65
converting variable values into, 65
returning geometries, 60–61
JSON_ARRAY() function, 45–46
JSON_ARRAY_APPEND() function, 26–28
JSON_ARRAY_INSERT() function, 28–29
JSON_CONTAINS_PATH() function, 20–22
JSON_DECODE() function, 64–65
JSON_DEPTH() function, 38
JSON_ENCODE() function, 65
JSON_EXTRACT() function, 35, 47, 100
JSON_INSERT() function, 30–31
JSON_KEYS() function, 16–17, 20
JSON_LENGTH() function, 38–39
JSON_MERGE() function, 35–38
JSON_MERGE_PRESERVE() function, 35–36, 38
JSON_OBJECT() function, 44–45
JSON_OBJECT_AS_ ARRAY option, 64
JSON_PATCH() function, 35–36
JSON_PRETTY() function, 15–16
JSON_REMOVE() function, 32–33
JSON_REPLACE() function, 31–32
JSON_SEARCH() function, 22–23
JSON_SET() function, 30, 33–34
JSON_STORAGE_FREE() function, 41
JSON_STORAGE_SIZE() function, 40
JSON_TABLE() function, 49–51
JSON_TYPE() function, 39
JSON_UNQUOTE() function, 34–35
JSON_VALID() function, 40
K
keys
casting and, 46
considerations, 13
displaying, 16–17
JSON objects and, 44–45
named, 13–14
quoting/unquoting, 34–35
searching for, 20–21
sorting and, 88–89
top-level, 20
key/value pairs
adding, 84
extracting values from, 51
JSON objects and, 44–45
listing, 17
missing, 49–50
paths and, 14
removing, 85
L
limit() function, 87–88
loading JSON data, 67–75
M
MAC OS–based systems, 3
MongoDB dataset, 104
MongoDB document store, 73
MySQL
casting from, 46–47
connections, 79
considerations, 3–4
Developer Milestone Release, 48
installation of, 4
language connectors, 94
use of examples, 5–6
vs. X Protocol, 78–79
MySQL data type, 7. See also data types
MySQL databases, 71
MySQL Document Store, 6, 77–101
bind() function, 89–90
collections/documents, 81–83
CRUD operations, 80, 83–86, 99
dropping collections, 92
find() function, 86–88
indexing collections, 90–92
JavaScript interpreter, 98
programming examples, 94–96
programming with, 93–101
relational tables, 99–101
sort() function, 88–89
vs. traditional SQL, 97–98
X DevAPI, 78–80
MySQL Documentation site, 4
MySQL Labs, 104
MySQL Shell (mysqlsh), 78–82, 98
MySQL syntax checker, 73
MySQL X DevAPI. See X DevAPI
mysqlsh (MySQL Shell), 78–82, 98
N
nested arrays, 39
nested data, 50–51
nested objects, 39
NESTED PATH option, 50–51
.Net language, 94
Node.JS coding example, 95–96
Node.JS language, 94
non-JSON data. See also data
creating arrays from, 45–46
creating JSON-formatted data from, 44
output of, 48–49
using JSON_OBJECT with, 44–45
NoSQL document store, 73
O
object-relational mapping (ORM), 93
objects
vs. arrays, 26
creating, 44–45
embedded, 78
mapping, 78
merging, 36–38
names, 35–38
nested, 39
overview, 2–3
returning as arrays, 20
using with non-JSON data, 45
ORM (object-relational mapping), 93
P
paths, 13–18
casting and, 46
considerations, 14
length of, 38–39
nested data and, 50–51
overview, 13–14
searching for, 21–22
specifying, 17
PCRE (Perl Compatible Regular Expressions) parser, 71
PECL (PHP Extension Community Library), 94
period (.), 14
Perl Compatible Regular Expressions (PCRE) parser, 71
PHP Extension Community Library (PECL), 94
programming
examples, 94–96
JavaScript, 98
with MySQL Document Store, 93–101
PHP. See PHP entries
Python. See Python entries
programming examples, 94–96
Python language, 94
Q
R
records
adding/removing, 86
considerations, 73
examining, 14–15
indexes and, 90
key/value pairs, 50
selecting, 14–15
REGEX (regular expressions), 9
regular expressions (REGEX), 9
relational tables, 48–49, 99–101
Request For Comment. See RFC entries
resources, 103–104
restaurant collection, 73–74
RFC 7946, 59
S
schemas, 81–82
searches
considerations, 8–9
filtering, 86–88
find() function, 86–88
keys, 20–21
paths, 21–22
REGEX and, 10
speeding, 10
values, 22–23
servers
collections and, 82
indexes and, 90
JSON data and, 11
loading data into, 74
session types, 80
virtual, 4
session types, 80–81
SHOW CREATE TABLE, 55
skip() function, 87–88
sort() function, 88–89
sort key, 88–89
sorting, 88–89
spatial data, 59
Spatial Reference System Identifier (SRID) argument, 60–61
SQL
coding example, 97–98
vs. MySQL Document Store, 97–98
SQL mode, 80
SQL queries, 78
SQL RDBMS, 59
square brackets ([ ]), 2, 3, 26
SRID (Spatial Reference System Identifier) argument, 60–61
ST_AsGeoJSON function, 61–62
ST_GeomFromGeoJSON function, 60–61
ST_GeomFromText options, 61–62
stream editors, 73
string data, 8–10
strings, 3
Structured Query Language. See SQL
T
tables
casting documents as, 100
vs. collections, 99–100
extracting data from, 100
InnoDB, 73
text fields, 8
tokens, 2
TRUNCATE command, 29–30
U
Uniform Resource Identifier (URI), 80
unique_id key, 92
unset() function, 85
URI (Uniform Resource Identifier), 80
UTF8MB4 character set, 3
V
values
considerations, 13
quoting/unquoting, 34–35
reporting contents of, 39
searching for, 22–23
variables, 89–90
virtual servers, 4
W
wildcards (*), 9, 18, 27, 46–47
Windows systems, 3
WITH VALIDATION clause, 58
world database, 4
world_x database, 4–5, 14–16, 99, 104
wrappers, 69–70
X
X DevAPI, 78–80
Google Protocol Buffers, 103–104
language connectors, 94
mysqlsh shell, 78–80
overview, 78
X Protocol, 78–80
Z
ZIP code dataset, 68–71
18.226.4.191