For an instructor lead, in-depth look at learning SQL click below.
If you’ve been working with modern web applications, chances are that you’re familiar with JSON (JavaScript Object Notation), a popular data format for transferring data between a server and a client. Although JSON is closely associated with JavaScript, its simplicity, compactness, and clarity have made it a favorite among many SQL users. Today, let’s delve into the subject of performing CRUD (Create, Read, Update, and Delete) operations on JSON data using SQL, a valuable skill for any SQL developer to possess.
Create (INSERT) operation
While working with JSON data in SQL, you’ll often want to create and insert new JSON objects into your database. You use the INSERT INTO statement to do this. Here’s an example:
|
1 2 3 4 |
INSERT INTO orders (info) VALUES ('{"customer": "John Doe", "items": {"product": "Apple", "qty": 10}}'); |
In the above SQL query, we are inserting a JSON object into the ‘info’ column of the ‘orders’ table.
Read (SELECT) Operation
Next, SQL allows for the retrieval and display of JSON data through the SELECT statement, in much the same manner as it does with traditional data types. Let’s have a look:
|
1 2 3 4 5 |
SELECT info->'customer' FROM orders WHERE CAST(info->'items'->>'qty' AS INTEGER) > 10; |
This query selects the ‘customer’ field from the ‘info’ JSON object in the ‘orders’ table and returns only those records where the ‘qty’ field under ‘items’ > 10.
Update (UPDATE) Operation
You can also manipulate JSON data in SQL by using the UPDATE statement. In this case, you can use the JSONB_SET function. It locates and changes specified values within the JSON object. Here’s an example:
|
1 2 3 4 5 |
UPDATE orders SET info = jsonb_set(info, '{items,qty}', '5') WHERE info->'customer' = 'John Doe'; |
Above query changes the ‘qty’ in the ‘items’ field in data linked to ‘John Doe’ to 5.
Delete (DELETE) Operation
Lastly, for the deletion of records, SQL permits the use of a DELETE statement. A DELETE statement could look like this:
|
1 2 3 4 |
DELETE FROM orders WHERE CAST(info->'items'->>'qty' AS INTEGER) < 5; |
This query will delete all orders where the ‘qty’ in ‘items’ is less than 5.
It’s important to mention that while SQL does support JSON data, not all databases may support all JSON functionalities. Always refer to the specific documentation of the SQL version you’re using for accurate information.
Conclusion
Being adept at CRUD operations on JSON data can significantly expand your ability to work with SQL databases, particularly in an era where JSON has become increasingly important. Now that you know these operations in SQL for handling JSON data, you should be able to implement these in your database interactions.