For an instructor lead, in-depth look at learning SQL click below.
With the increasing popularity and use of JSON (JavaScript Object Notation) as a data interchange format, it is essential to know how to effectively work with JSON data in SQL. SQL’s JSON support comes with a diverse set of operators and functions that allow you to easily parse, construct, and otherwise manipulate JSON data.
SQL and JSON Compatibility
Before we dive into the examples, it’s important to note that not all SQL databases natively support JSON. Some of the most popular ones that do include PostgreSQL, MySQL, and SQL Server. For the purpose of these examples, I’ll be using SQL server and its JSON related functions like JSON_VALUE, JSON_QUERY, JSON_MODIFY and OPENJSON.
Storing JSON Data in SQL
Storing JSON data in SQL databases is pretty straightforward. In most cases, JSON data is stored as simple text. Here’s a simple example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE Orders ( OrderId int, OrderDetails NVARCHAR(MAX) ); INSERT INTO Orders VALUES (1, N'{ "Customer": "John Doe", "Items": [ {"Product": "Apple", "Quantity": 3}, {"Product": "Orange", "Quantity": 2} ], "Payment": "CreditCard" }'); |
Extracting JSON Data in SQL
The real magic happens when fetching and manipulating JSON data. SQL Server provides JSON_VALUE and JSON_QUERY functions to extract scalar values or objects/arrays.
|
1 2 3 4 5 6 7 |
SELECT JSON_VALUE(OrderDetails, '$.Customer') AS Customer, JSON_QUERY(OrderDetails, '$.Items') AS Items FROM Orders WHERE OrderId = 1; |
Modifying JSON Data in SQL
You can also modify JSON data using the JSON_MODIFY function.
|
1 2 3 4 5 |
UPDATE Orders SET OrderDetails = JSON_MODIFY(OrderDetails, '$.Payment', 'PayPal') WHERE OrderId = 1; |
Parsing JSON Data in SQL
Finally, we can parse JSON arrays into a tabular format using the OPENJSON function:
|
1 2 3 4 5 6 7 8 |
SELECT OrderId, JSON_VALUE(j.value, '$.Product') AS Product, JSON_VALUE(j.value, '$.Quantity') AS Quantity FROM Orders CROSS APPLY OPENJSON(OrderDetails, '$.Items') AS j; |
Conclusion
With the growth of JSON as a data format, knowing how to manipulate such data in SQL becomes essential for any SQL developer. Ultimately, the ability to parse, construct and manipulate JSON data provides greater flexibility in our data handling and opens the door to more sophisticated and dynamic SQL queries.