SQL query Vs BSON (Binary JSON)

 

Aspect	SQL Query	BSON Format
Definition	A structured query language used to retrieve and manipulate data stored in a relational database management system (RDBMS).	A binary representation of JSON-like documents used in NoSQL databases, particularly MongoDB.
Syntax	Follows a standardized syntax with keywords like SELECT, FROM, WHERE, JOIN, etc.	Follows a JSON-like syntax, representing documents with key-value pairs and nested structures.
Usage	Used to query relational databases and perform operations such as selecting, inserting, updating, and deleting data.	Used to store and retrieve data in NoSQL databases, particularly in MongoDB, where documents are stored in BSON format.
Data Model	Follows a tabular data model with rows and columns, and supports ACID transactions.	Follows a document-oriented data model, where data is stored as collections of documents.
Schema	Relational databases have a predefined schema that defines the structure of the tables and relationships between them.	NoSQL databases like MongoDB have a flexible schema, allowing documents in a collection to have varying structures.
Query Language	Supports SQL query language constructs like SELECT, INSERT, UPDATE, DELETE, JOIN, WHERE, etc.	Supports query operators and methods specific to the MongoDB query language, such as find(), findOne(), $match, $project, $group, etc.
Join Operations	Supports various types of joins (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN) to combine data from multiple tables.	NoSQL databases typically denormalize data and avoid complex joins, favoring document embedding or referencing.
Atomicity	Supports ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure data integrity and reliability.	NoSQL databases may offer eventual consistency, where data consistency is guaranteed over time, but not necessarily immediately.
Scalability	Relational databases can scale vertically (by adding more resources to a single server) or horizontally (by sharding or replication).	NoSQL databases are designed for horizontal scalability, allowing them to handle large volumes of data and high throughput.
Examples	MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite, etc.	MongoDB, Couchbase, Cassandra, Amazon DynamoDB, Couc

SQL Query Example: Consider a scenario where you have a table named employees in a relational database, and you want to retrieve the names and salaries of all employees whose salary is greater than 50000. Here's how you would write a SQL query for this:

SELECT name, salary FROM employees WHERE salary > 50000;

This SQL query selects the name and salary columns from the employees table where the salary is greater than 50000.

BSON Document Example: Now, let's represent a similar data structure using BSON format. Assume we have a collection named employees in a MongoDB database, and each document represents an employee record. Here's how you might represent an employee document in BSON format:

{ "_id": ObjectId("60e4c89c281de3b89fc4c3d2"), "name": "John Doe", "salary": 60000, "department": "Engineering" }

This BSON document represents an employee named "John Doe" with a salary of 60000 and belongs to the "Engineering" department. The _id field is a unique identifier generated by MongoDB for each document.