website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

JSON Type

9min

This article will teach you how to work with JSON data types with some useful examples.

Overview

JSON 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.

Syntax

The JSON data type in Oxla has the following syntax:

Syntax
|
variable_name JSON  


Examples

1) Create a Table

First, 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  
);  


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 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}}');


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 Data

Use the SELECTcommand to retrieve the orders table's data.

Display the table
|
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"}}  |
+--------------------------------------------------------------------------+


Note: It is normal for the JSON type’s result to look disordered.



Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
Numeric Type
NEXT
SQL Functions
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Syntax
Examples
1) Create a Table
2) Insert Data
3) Retrieve Data

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla