MYSQL JSON
MYSQL
The MySQL JSON data type allows efficient storage, management, and querying of JSON (JavaScript Object Notation) documents in a relational database context.
MySQL JSON data type
➔ JavaScript Object Notation (JSON) is a lightweight text-based file format like YAML or XML that simplifies data transfer and exchange.
➔ In MySQL JSON data type is useful:
- Where attributes can vary from row to row.
- Where the data does not fit well into a flat relational structure.
- Where the data structure may be inconsistent or evolve over time.
➔ JSON documents are converted to an internal binary format for fast read access to specific elements without having to parse the entire document.
➔ The -> operator is short for the JSON_EXTRACT() function, and the -> operator extracts and unquotes the result.
Create table:
Example
/* Create table */
CREATE TABLE myproducts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
details JSON
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
/* Insert values */
INSERT INTO myproducts (name, details) VALUES
('Laptop', '{"brand": "Dell", "screen_size": "15-inch", "price": 999.99}');
INSERT INTO myproducts (name, details) VALUES
('Desktop', '{"brand": "HP", "screen_size": "19-inch", "price": 599.99}');
Copy the code and try it out in your learning environment.
Describe table structure:
Example
/* Check table structure */
DESCRIBE myproducts;
Copy the code and try it out practically in your learning environment.
Table structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | auto_increment | |
| name | varchar(255) | NO | |||
| details | json | YES |
Query value:
Example
/* Query data */
SELECT name, JSON_EXTRACT(details, '$[0]') FROM myproducts;
/* -> operator equivalent for JSON_EXTRACT() function */
SELECT name, details->'$[0]' FROM myproducts;
Copy the code and try it out practically in your learning environment.
Query value:
Example
/* Retrieving query data */
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(details, '$[0]')) FROM myproducts;
/* ->> operator equivalent for JSON_UNQUOTE(JSON_EXTRACT()) */
SELECT name, details->>'$[0]' FROM myproducts;
SELECT name, details->>'$.brand' AS brand FROM myproducts WHERE details->>'$.brand' = 'Dell';
Copy the code and try it out practically in your learning environment.
Update data:
Example
/* Update data */
SELECT id, name, details->>'$[0]' FROM myproducts;
UPDATE myproducts
SET details = JSON_SET(details, '$.price', 1150)
WHERE id= 1;
/* Check Update data */
SELECT id, name, details->>'$[0]' FROM myproducts;
Copy the code and try it out practically in your learning environment.
We can use JSON_ARRAY_APPEND or JSON_ARRAY_INSERT to update JSON.