SQL OPENJSON: A Comprehensive Guide for Beginners and Intermediates

Welcome to our SQL OPENJSON tutorial! In this lesson, we'll explore the OpenJSON function, a powerful tool for handling JSON data within SQL Server.

Understanding JSON and SQL Server

JSON (JavaScript Object Notation) is a lightweight data interchange format used to store and transport data. SQL Server, a relational database management system, can work with JSON data to provide flexibility and efficiency in handling complex, nested data structures.

Introducing SQL Server's OPENJSON Function

The OPENJSON function allows SQL Server to parse JSON data and treat it as a table, making it easier to manipulate and query. Let's see how it works!

sql

SELECT * FROM OPENJSON('[{"name": "John", "age": 30},{"name": "Jane", "age": 28}]')
WITH (
    name NVARCHAR(50),
    age INT
)

In this example, we're using OPENJSON to parse a JSON array containing two objects. We specify the schema (with) for the expected structure of the JSON data, and SQL Server returns the data as a table.

💡 Pro Tip: Remember to single-quote your JSON string.

Practical Application

Let's say we have a JSON object representing user data for an e-commerce website:

json

{
  "users": [
    {
      "id": 1,
      "name": "John",
      "email": "john@example.com",
      "purchases": [
        {
          "productId": 1,
          "quantity": 2
        },
        {
          "productId": 3,
          "quantity": 1
        }
      ]
    },
    {
      "id": 2,
      "name": "Jane",
      "email": "jane@example.com",
      "purchases": []
    }
  ]
}

We can use OPENJSON to query this data:

sql

SELECT * FROM OPENJSON('[{"id": 1, "name": "John", "email": "john@example.com", "purchases": [{"productId": 1, "quantity": 2}, {"productId": 3, "quantity": 1}]},{"id": 2, "name": "Jane", "email": "jane@example.com", "purchases": []}]')
WITH (
    id INT,
    name NVARCHAR(50),
    email NVARCHAR(100),
    purchases NVARCHAR(MAX) AS JSON
)

This query returns a table with the user data and the JSON array of purchases as a separate column, allowing us to further query the purchases data using JSON functions.

Advanced Example: Grouping Purchases

Let's say we want to find out how many unique products each user has purchased:

sql

SELECT
    id,
    name,
    email,
    JSON_VALUE(purchases, '$.productId') AS productId,
    JSON_VALUE(purchases, '$.quantity') AS quantity
FROM OPENJSON('[{"id": 1, "name": "John", "email": "john@example.com", "purchases": [{"productId": 1, "quantity": 2}, {"productId": 3, "quantity": 1}]},{"id": 2, "name": "Jane", "email": "jane@example.com", "purchases": []}]')
WITH (
    id INT,
    name NVARCHAR(50),
    email NVARCHAR(100),
    purchases NVARCHAR(MAX) AS JSON
)
GROUP BY id, productId

This query groups the purchases by user and product, allowing us to see the unique products each user has purchased.

📝 Note: JSON_VALUE function is used to extract values from the JSON array.

Quiz Time!

That's it for today! We hope you found this SQL OPENJSON tutorial helpful. Stay tuned for more lessons on SQL and programming here at CodeYourCraft. Happy coding! 🎉