Parse Text to JSON – SQL Tutorial

In the modern age of data-driven decision-making, JSON (JavaScript Object Notation) has become a standard format for data exchange due to its simplicity and flexibility. SQL Server, a leading database management system, has evolved to provide robust support for JSON. One common use case is converting traditional text data into JSON format to facilitate seamless integration with modern applications, APIs, or analytics platforms.

In this blog, we’ll explore how SQL Server enables text-to-JSON conversion, discuss practical examples, and provide tips for effective implementation.

Why Parse Text to JSON in SQL Server?

JSON is lightweight and human-readable, making it a preferred choice for representing structured data. Converting text to JSON in SQL Server is valuable in several scenarios:

Integration with APIs: Many APIs require JSON-formatted data as input or output.

Data Transformation: Converting legacy text data to JSON facilitates migration to modern systems.

Analytics: JSON format simplifies parsing and analysis in tools like Power BI or third-party analytics software.

Key SQL Server Features for JSON Handling

SQL Server introduced native JSON support starting with SQL Server 2016. Key functionalities include:

FOR JSON Clause: Converts query results to JSON format.

OPENJSON Function: Parses JSON data into relational format.

JSON_VALUE and JSON_QUERY: Extract specific values or JSON fragments.

ISJSON: Validates JSON format.

For converting text to JSON, the FOR JSON clause and string manipulation functions play a pivotal role.

Step-by-Step Guide: Converting Text to JSON in SQL Server

Let’s dive into practical examples that demonstrate converting plain text into JSON.

Example 1: Simple Conversion from Text Data

Assume you have a table named Employees with the following data:

EmployeeID	Name	Department	Salary
1	John Doe	HR	55000
2	Jane Smith	IT	70000

You want to convert this data into JSON format. Here’s how you can use the FOR JSON clause:

SELECT EmployeeID, Name, Department, Salary
FROM Employees
FOR JSON AUTO;

Output:

[
    {"EmployeeID":1,"Name":"John Doe","Department":"HR","Salary":55000},
    {"EmployeeID":2,"Name":"Jane Smith","Department":"IT","Salary":70000}
]

The FOR JSON AUTO clause automatically structures the JSON based on the table’s hierarchy. For greater control, use FOR JSON PATH.

Example 2: Converting a Delimited Text String to JSON

If your data exists as delimited text, SQL Server’s string manipulation functions combined with JSON functions can help. Consider the following string:

'1,John Doe,HR,55000;2,Jane Smith,IT,70000'

To convert this into JSON:

Split the String into Rows: Use a table-valued function or a Common Table Expression (CTE) to split the text.

Map Rows to JSON: Use FOR JSON to structure the output.

Here’s an example query:

WITH EmployeeData AS (
    SELECT value AS RawData
    FROM STRING_SPLIT('1,John Doe,HR,55000;2,Jane Smith,IT,70000', ';')
)
SELECT
    JSON_QUERY('[' +
    STRING_AGG('{"EmployeeID":' + QUOTENAME(PARSENAME(value,4),'"') +
               ',"Name":' + QUOTENAME(PARSENAME(value,3),'"') +
               ',"Department":' + QUOTENAME(PARSENAME(value,2),'"') +
               ',"Salary":' + QUOTENAME(PARSENAME(value,1),'"') + '}', ',') + ']')
AS JsonOutput
FROM EmployeeData;

Example 3: Nested JSON Structures

Sometimes, you may need to create nested JSON structures. Let’s modify the Employees table to include multiple phone numbers:

EmployeeID	Name	Phones
1	John Doe	1234567890,9876543210
2	Jane Smith	1122334455,9988776655

Here’s how to convert it into nested JSON:

SELECT 
    EmployeeID, 
    Name, 
    JSON_QUERY(
        '[' + STRING_AGG('{"Phone":"' + value + '"}', ',') + ']'
    ) AS Phones
FROM (
    SELECT EmployeeID, Name, value
    FROM Employees
    CROSS APPLY STRING_SPLIT(Phones, ',')
) PhoneData
GROUP BY EmployeeID, Name;

Output:

[
    {"EmployeeID":1,"Name":"John Doe","Phones":[{"Phone":"1234567890"},{"Phone":"9876543210"}]},
    {"EmployeeID":2,"Name":"Jane Smith","Phones":[{"Phone":"1122334455"},{"Phone":"9988776655"}]}
]

Best Practices for Converting Text to JSON

Validate Input: Use functions like ISJSON to validate the JSON structure before processing.

Optimize Queries: Large text data conversions can be resource-intensive. Optimize by indexing and limiting the scope of queries.

Test for Performance: Benchmark JSON transformations on large datasets to ensure acceptable performance.

Leverage Functions: Use SQL Server’s JSON functions for precise manipulation and querying.

Conclusion

Converting text to JSON in SQL Server is a straightforward process, thanks to its native JSON capabilities. Whether you’re working with structured tables, delimited strings, or nested data, SQL Server offers versatile tools to simplify the transformation.

By mastering these techniques, you can modernize data workflows, integrate seamlessly with APIs, and improve the compatibility of your SQL Server databases with today’s data-centric applications. JSON in SQL Server is a powerful bridge between traditional databases and modern systems—unlock its potential for your next project!