JSON Type

9min
This article will teach you how to work with JSON data types with some useful examples.
OverviewJSON stands for JavaScript Object Notation. It is an open standard format that consists of key-value pairs to transport data between a server and a web application. 
SyntaxThe JSON data type in Oxla has the following syntax:
Syntax
|
variable_name JSON  
variable_name JSON  
﻿
Examples1) Create a TableFirst, create the orders table using the below command:
For further details about JSON type, look at the examples below.
Create a table
|
CREATE TABLE orders (  
    orders_Detail JSON  
);  
CREATE TABLE orders (  
    orders_Detail JSON  
);  
﻿
This will create a table with the orders_Detailcolumn to store key-value pairs of data.
2) Insert DataNext, insert data into the orders table as follows:
Insert data
|
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}}');
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 purchase the product.
items: it will store the order details, product & qty.
3) Retrieve DataUse the SELECTcommand to retrieve the orders table's data.
Display the table
|
SELECT * FROM orders;
SELECT * FROM orders;
﻿
You will get the following output:
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"}}  |
+--------------------------------------------------------------------------+
+--------------------------------------------------------------------------+
| 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"}}  |
+--------------------------------------------------------------------------+
﻿
Note: It is normal for the JSON type’s result to look disordered.
﻿
Updated 11 May 2023
Did this page help you?
Yes
Numeric Type
SQL Functions
Docs powered by archbee
TABLE OF CONTENTS