MySQL and JSON A Practical Programming Guide 2018

Practical instruction on using JavaScript Object Notation (JSON) with MySQL This hands-on guide teaches, step by step, how to use JavaScript Object Notation (JSON) with MySQL. Written by a MySQL Community Manager for Oracle, MySQL and JSON: A Practical Programming Guide shows how to quickly get started using JSON with MySQL and clearly explains the latest tools and functions. All content is based on the author’s years of interaction with MySQL professionals. Throughout, real-world examples and sample code guide you through the syntax and application of each method. You will get in-depth coverage of programming with the MySQL Document Store. •See how JavaScript Object Notation (JSON) works with MySQL •Use JSON as string data and JSON as a data type •Find the path, load data, and handle searches with REGEX •Work with JSON and non-JSON output •Build virtual generated columns and stored generated columns •Generate complex geometries using GeoJSON •Convert and manage data with JSON functions •Access JSON data, collections, and tables through MySQL Document Store 1 Introduction JSON MySQL The Example Database How to Use This Book 2 JSON as String Data vs. JSON as a Data Type JSON String Data The JSON Data Type 3 Finding the Path Examining the world_x Data Seeing the Keys Path Digging Deeper 4 Finding and Getting Data All Keys Searching for a Key Searching for a Path Searching for a Value 5 Changing Data Using Arrays Appending Arrays Inserting into an Array Using TRUNCATE Before Adding New Data Using JSON_INSERT Using JSON_REPLACE JSON_REMOVE JSON_SET JSON_UNQUOTE The Three JSON_MERGE Functions JSON_MERGE JSON_MERGE_PRESERVE JSON_DEPTH JSON_LENGTH JSON_TYPE JSON_VALID JSON_STORAGE_SIZE JSON_STORAGE_FREE 6 JSON and Non-JSON Output JSON-Formatted Data JSON_OBJECT JSON_ARRAY Casting Non-JSON Output Missing Data Nested Data 7 Generated Columns Using Generated Columns Columns Generated from JSON Generated Columns: Common Errors 8 GeoJSON ST_GeomFromGeoJSON ST_AsGeoJSON 9 PHP’s JSON Functions JSON_DECODE JSON_ENCODE 10 Loading JSON Data From Download to Database Step 1: Examine the Data Step 2: Create the Table Step 3: Load the Data Using a Wrapper Step 4: Double-Check the Data jq: JSON CLI Parser With No Arguments Select Certain Fields The Restaurant Collection 11 The MySQL Document Store The X DevAPI mysqlsh Connections Session Types Collections and Documents CRUD: Create, Replace, Update, Delete Filtering Find Sorting Binding Indexing Collections Dropping a Collection 12 Programming with the MySQL Document Store Programming Examples Python Example Node.JS Example PHP Example Traditional SQL vs. MySQL Document Store The MySQL Shell and JavaScript Relational Tables Both Relational and Document Document as Relational A Additional Resources Index