SQL Data Types
JSON Type
SQL Data Types
JSON Type
Overview
JSON stands for JavaScript Object Notation. It is an open standard format with key-value pairs to transport data between a server and a web application.
Syntax
The JSON data type in Oxla has the following syntax:
variable_name JSON
Examples
1. Create a Table
First, create the orders table using the below command:
CREATE TABLE orders (
orders_Detail JSON
);
This will create a table with the orders_Detailcolumn to store key-value pairs of data.
2. Insert Data
Next, insert data into the orders table as follows:
INSERT INTO orders (orders_Detail)
VALUES
('{ "customer": "Dean Smith", "items": {"product": "cup","qty": 2}}'),
('{ "customer": "Sissy Kate", "items": {"product": "knife","qty": 1}}'),
('{ "customer": "Emma Stone", "items": {"product": "spoon","qty": 4}}'),
('{ "customer": "Chris Bale", "items": {"product": "fork","qty": 5}}'),
('{ "customer": "Mike Stuart", "items": {"product": "spatula","qty": 2}}');
This will insert data values where orders_Detailhas the following keys:
-
customer: it will store a customer’s data who purchased the product. -
items: it will store the order details,product&qty.
3. Retrieve Data
Use the SELECT command to retrieve the orders table’s data.
SELECT * FROM orders;
You will get the following output:
+--------------------------------------------------------------------------+
| orders_detail |
+--------------------------------------------------------------------------+
| {"customer":"Dean Smith","items":{"qty":2.000000,"product":"cup"}} |
| {"customer":"Sissy Kate","items":{"product":"knife","qty":1.000000}} |
| {"customer":"Emma Stone","items":{"qty":4.000000,"product":"spoon"}} |
| {"customer":"Chris Bale","items":{"product":"fork","qty":5.000000}} |
| {"customer":"Mike Stuart","items":{"qty":2.000000,"product":"spatula"}} |
+--------------------------------------------------------------------------+
It is normal for the JSON type’s result to look disordered.