MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
JSON Document Functions
Describes some commonly used JSON Document functions like, JSON_VALID(), JSON_TYPE(), JSON_ARRAY(), JSON_OBJECT(), JSON_EXTRACT(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_INSERT().
MySQL supports a number of built-in functions that allows you to manipulate JSON documents.
JSON_VALID(string) - Returns true, if the given string is a valid JSON string.
JSON_TYPE(json) - Returns the JSON type name of the given JSON Document.
JSON_ARRAY(value1, value2, ...) - Creates a JSON array with the given values.
JSON_OBJECT(key1, value1, key2, value2, ...) - Creates a JSON object with the given key value pairs.
JSON_EXTRACT(json, path) - Extracts a JSON sub-document from a given JSON Document at the given path location.
JSON_REMOVE(json, path) - Removes a JSON sub-document from a given JSON document at the given path location.
JSON_REPLACE(json, path, sub) - Replaces a JSON sub-document in a given JSON Document at the given path location.
JSON_SET(json, path, sub) - Same as JSON_REPLACE() except that the given new JSON sub-document will be added, if the path does not exist.
JSON_INSERT(json, path, sub) - Inserts the given new sub-document to a given JSON document at the given path location, only if path does not exist.
Examples of JSON document functions, JsonFunctions.sql:
-- JsonFunctions.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- SELECT JSON_VALID('null'); SELECT JSON_VALID('NULL'); SELECT JSON_TYPE('null'); SELECT JSON_TYPE('"null"'); SELECT JSON_ARRAY('a', 1, NOW()); SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'); SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'); SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]'); SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]'); SELECT JSON_REMOVE('[1, 2, 3, 4, 5]', '$[3]'); SELECT JSON_REPLACE('{"id": 14, "name": "Aztalan"}', '$.name', "Yahoo"); SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[3]', 'Four'); SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[8]', 'Nine'); SELECT JSON_SET('[1, 2, 3, 4, 5]', '$[3]', 'Four'); SELECT JSON_SET('[1, 2, 3, 4, 5]', '$[8]', 'Nine'); SELECT JSON_INSERT('[1, 2, 3, 4, 5]', '$[3]', 'Four'); SELECT JSON_INSERT('[1, 2, 3, 4, 5]', '$[8]', 'Nine');
Output:
JSON_VALID('null') 1 JSON_VALID('NULL') 0 JSON_TYPE('null') NULL JSON_TYPE('"null"') STRING JSON_ARRAY('a', 1, NOW()) ["a", 1, "2019-07-23 22:59:43.000000"] JSON_OBJECT('key1', 1, 'key2', 'abc') {"key1": 1, "key2": "abc"} JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') "Aztalan" JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') [3, 4, 5] JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') [2, 3, 4] JSON_REMOVE('[1, 2, 3, 4, 5]', '$[3]') [1, 2, 3, 5] JSON_REPLACE('{"id": 14, "name": "Aztalan"}', '$.name', "Yahoo") {"id": 14, "name": "Yahoo"} JSON_REPLACE('[1, 2, 3, 4, 5]', '$[3]', 'Four') [1, 2, 3, "Four", 5] JSON_REPLACE('[1, 2, 3, 4, 5]', '$[8]', 'Nine') [1, 2, 3, 4, 5] JSON_SET('[1, 2, 3, 4, 5]', '$[3]', 'Four') [1, 2, 3, "Four", 5] JSON_SET('[1, 2, 3, 4, 5]', '$[8]', 'Nine') [1, 2, 3, 4, 5, "Nine"] JSON_INSERT('[1, 2, 3, 4, 5]', '$[3]', 'Four') [1, 2, 3, 4, 5] JSON_INSERT('[1, 2, 3, 4, 5]', '$[8]', 'Nine') [1, 2, 3, 4, 5, "Nine"]
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux