This article shows how to split JSON data in a SQL Server columns. To split JSON data in a SQL Server columns, you can use the OPENJSON function, which can parse JSON strings into tabular format.

Example

Here are the steps you can follow:

1. Create table to store JSON data

Create a new table to store the parsed JSON data, with columns that match the JSON keys you want to extract. For example, if your JSON data has keys "id", "name", and "age", you can create a table with columns id, name, and age.

CREATE TABLE MyJsonData (
id INT,
name VARCHAR(50),
age INT
);

2. Use the OPENJSON function

Use the OPENJSON function to extract the JSON data into a table. The OPENJSON function takes two arguments: the JSON string to parse, and a path expression that specifies which elements to extract. In this case, you can use the $ path expression to extract all elements at the root level.

INSERT INTO MyJsonData
SELECT * FROM OPENJSON('[
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "age": 28}
]') WITH (
id INT '$.id',
name VARCHAR(50) '$.name',
age INT '$.age'
);

This will insert two rows into the MyJsonData table: First row with the values 1, "John", and 30. Second row with the values 2, "Jane", and 28.

3. JSON stored in a column in another table

If your JSON data is stored in a column in another table, you can use the same approach to extract the data into a new table. For example, if you have a table called MyTable with a column called JsonData, you can use the following query to extract the JSON data into the MyJsonData table:

INSERT INTO MyJsonData
SELECT * FROM MyTable
CROSS APPLY OPENJSON(JsonData)
WITH (
id INT '$.id',
name VARCHAR(50) '$.name',
age INT '$.age'
);

This will extract the JSON data from the JsonData column of each row in the MyTable table, parse it using the OPENJSON function, and insert the resulting rows into the MyJsonData table.

By using the OPENJSON function in SQL Server, you can easily parse and extract JSON data into a relational format that can be stored and queried using traditional SQL techniques.