O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MySQL and JSON: A Practical Programming Guide

Book Description

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

Table of Contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents
  6. Acknowledgments
  7. 1 Introduction
    1. JSON
    2. MySQL
    3. The Example Database
    4. How to Use This Book
  8. 2 JSON as String Data vs. JSON as a Data Type
    1. JSON String Data
    2. The JSON Data Type
  9. 3 Finding the Path
    1. Examining the world_x Data
    2. Seeing the Keys
    3. Path
    4. Digging Deeper
  10. 4 Finding and Getting Data
    1. All Keys
    2. Searching for a Key
    3. Searching for a Path
    4. Searching for a Value
  11. 5 Changing Data
    1. Using Arrays
    2. Appending Arrays
    3. Inserting into an Array
    4. Using TRUNCATE Before Adding New Data
    5. Using JSON_INSERT
    6. Using JSON_REPLACE
    7. JSON_REMOVE
    8. JSON_SET
    9. JSON_UNQUOTE
    10. The Three JSON_MERGE Functions
    11. JSON_MERGE
    12. JSON_MERGE_PRESERVE
    13. JSON_DEPTH
    14. JSON_LENGTH
    15. JSON_TYPE
    16. JSON_VALID
    17. JSON_STORAGE_SIZE
    18. JSON_STORAGE_FREE
  12. 6 JSON and Non-JSON Output
    1. JSON-Formatted Data
    2. JSON_OBJECT
    3. JSON_ARRAY
    4. Casting
    5. Non-JSON Output
    6. Missing Data
    7. Nested Data
  13. 7 Generated Columns
    1. Using Generated Columns
    2. Columns Generated from JSON
    3. Generated Columns: Common Errors
  14. 8 GeoJSON
    1. ST_GeomFromGeoJSON
    2. ST_AsGeoJSON
  15. 9 PHP’s JSON Functions
    1. JSON_DECODE
    2. JSON_ENCODE
  16. 10 Loading JSON Data
    1. From Download to Database
      1. Step 1: Examine the Data
      2. Step 2: Create the Table
      3. Step 3: Load the Data Using a Wrapper
      4. Step 4: Double-Check the Data
    2. jq: JSON CLI Parser
      1. With No Arguments
      2. Select Certain Fields
    3. The Restaurant Collection
  17. 11 The MySQL Document Store
    1. The X DevAPI
      1. mysqlsh
    2. Connections
      1. Session Types
    3. Collections and Documents
    4. CRUD: Create, Replace, Update, Delete
    5. Filtering Find
    6. Sorting
    7. Binding
    8. Indexing Collections
    9. Dropping a Collection
  18. 12 Programming with the MySQL Document Store
    1. Programming Examples
      1. Python Example
      2. Node.JS Example
      3. PHP Example
    2. Traditional SQL vs. MySQL Document Store
    3. The MySQL Shell and JavaScript
    4. Relational Tables
      1. Both Relational and Document
      2. Document as Relational
  19. A Additional Resources
  20. Index