Index

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

' (single quote), 73, 74

'' (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

add() function, 83, 86

ALTER TABLE statement, 56–58

apostrophe ('), 73–74

arguments, 71–72

arrayAppend, 85

arrays

adding, 84

appending, 26–28, 85

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

backups, 85, 92

BASH scripts, 69

bind() function, 89–90

binding, 89–90

bitmasks, 60, 61–62

Boolean data, 50

C

C++ language, 94

casting, 46–47

cells, 14

CLI (Command Line Interface), 78

collections, 81–83

considerations, 78

creating, 55, 81–82

dropping, 92

finding, 82, 99

indexing, 90–92

overview, 81–82

removing, 92

restaurant collection, 73–74

vs. tables, 99–100

colon (:), 2, 89

columns, 53–59, 78

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

backups, 85, 92

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

examining, 68, 70–71

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

updating, 28, 33–34

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

relational, 29, 78

world, 4

world_x, 4–5, 14–16, 99, 104

datasets, 16, 49, 67–75, 104

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

pretty printing, 15–16, 71–72

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

EXISTS keyword, 49, 50

EXPLAIN command, 91

F

fields

selecting, 72–73

sorting, 88–89

fields() function, 88–89

find() function, 83, 86–88

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

considerations, 10, 53

creating, 90–92

generated columns and, 53, 56–58

speeding searches with, 10

unique/nonunique, 90–92

InnoDB storage engine, 78, 83

InnoDB tables, 73

INTEGER value, 47

Internet Engineering Task Force. See IETF

J

Java, 94

JavaScript, 78, 98

JavaScript interpreter, 78, 98

JavaScript mode, 80, 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

MySQL support for, 2, 4

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

overview, 2, 77–78

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

pretty printing, 15–16, 71–72

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

levels, 17, 18

named, 13–14

paths, 21–22, 23

primary, 55, 73, 78, 83

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

Linux systems, 3, 4, 73

loading JSON data, 67–75

M

MAC OS–based systems, 3

modify() function, 83, 85

MongoDB dataset, 104

MongoDB document store, 73

MySQL

casting from, 46–47

connections, 79

considerations, 3–4

Developer Milestone Release, 48

documentation on, 5, 6

installation of, 4

JSON support, 2, 4

language connectors, 94

use of examples, 5–6

versions, 2, 3, 4

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

connections, 79, 80–81, 96–97

CRUD operations, 80, 83–86, 99

dropping collections, 92

find() function, 86–88

indexing collections, 90–92

JavaScript interpreter, 98

MySQL Shell, 78–82, 98

overview, 6, 77–78

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

NULL values, 46, 48–49

O

object-relational mapping (ORM), 93

objects

vs. arrays, 26

creating, 44–45

embedded, 78

GeoJSON, 61–62, 64

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

path legs, 14, 17

paths, 13–18

casting and, 46

considerations, 14

keys, 21–22, 23

length of, 38–39

nested data and, 50–51

overview, 13–14

searching for, 21–22

specifying, 17

subpaths, 17, 18, 48

PCRE (Perl Compatible Regular Expressions) parser, 71

PECL (PHP Extension Community Library), 94

period (.), 14

Perl Compatible Regular Expressions (PCRE) parser, 71

PHP, 63–65, 94

PHP coding example, 96, 97–98

PHP Extension Community Library (PECL), 94

primary key, 55, 73, 78, 83

printing, 15–16, 71–72

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, 78, 94–95

Python interpreter, 78, 98

Python language, 94

Python mode, 80, 98

Q

quotes, double (''), 73, 74

quotes, single ('), 73, 74

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 databases, 29, 78

relational tables, 48–49, 99–101

remove() function, 83, 86

Request For Comment. See RFC entries

resources, 103–104

restaurant collection, 73–74

RFC 7159, 2, 35

RFC 7946, 59

S

scalars, 37, 38, 39

schemas, 81–82

scope, 14, 17

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

sed utility, 73, 74

servers

collections and, 82

considerations, 73, 79

indexes and, 90

JSON data and, 11

loading data into, 74

multiple, 78, 79

session types, 80

virtual, 4

session types, 80–81

SHOW CREATE TABLE, 55

single quote ('), 73, 74

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

considerations, 78–79, 93

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

creating, 26, 68–69

extracting data from, 100

InnoDB, 73

relational, 48–49, 99–101

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

UTF8MB4 strings, 39, 65

V

values

considerations, 13

NULL, 46, 48–49

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