If...
INSERT INTO TABLE_NAME
SELECT
STRING_AGG(COLUMN_NAME, ',')
FROM
TABLE_NAME
introduces an anti-pattern (i.e., can cause poor performance, incorrect results (kindly verify this one), and maintainability issues of the T-SQL queries);
How about this:
INSERT INTO TABLE_NAME(COLUMN_NAME)
SELECT
N'{"KEY_VALUE": [' + TBN.STR_AGG + '"]}' JSON_FORMAT_VALUE
FROM
(SELECT
STRING_AGG('"' + COLUMN_NAME, + '", ') STR_AGG
FROM
TABLE_NAME) TBN
This is my actual test sample query:
DECLARE @users TABLE(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
username NVARCHAR(100) NOT NULL,
email NVARCHAR(100) NOT NULL,
status VARCHAR(50)
);
DECLARE @features TABLE(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(200) NOT NULL,
is_enabled BIT
);
DECLARE @feature_user TABLE(
user_id INT NOT NULL,
feature_id INT NOT NULL
);
INSERT INTO @users(
username,
email,
status
)
VALUES(
N'john_doe',
N'[email protected]',
'active'
),
(
N'mark_daniels',
N'[email protected]',
'inactive'
),
(
N'alice_jane',
N'[email protected]',
'active'
);
INSERT INTO @features(
name,
description,
is_enabled
)
VALUES(
'notifications',
'Send notifications to users',
'TRUE'
),
(
'csv export',
'Export data to CSV format',
'FALSE'
),
(
'redesign landing page',
'Revamp the landing page layout',
'TRUE'
);
INSERT INTO @feature_user
VALUES(
1,
1
),
(
1,
2
),
(
1,
3
),
(
2,
1
),
(
2,
2
),
(
3,
3
)
-- Produces comma-delimited data structure
SELECT
u.id AS user_id,
u.username,
u.email,
u.status,
STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;
-- Produces denormalized data structure
SELECT
feature_data.user_id,
feature_data.username,
feature_data.email,
feature_data.status,
N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT
u.id AS user_id,
u.username,
u.email,
u.status,
STRING_AGG('"' + f.name, + '", ') feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;
-- The below query to check if the JSON data is valid
SELECT
ISJSON(N'{"feature_data": [' + feature_data.feature_names + '"]}') feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT
STRING_AGG('"' + f.name, + '", ') feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;
And the SELECT query outputs are:
Credits to the owner of the idea: How to Avoid Redundant Rows When Joining Tables in SQL?
However, this case-scenario reflects with my database setup.
Explanations: The JSON data will be decoded for later use in the SQL Server database with a compatibility level of 130 or greater, and I am using compatibility level 140 or SQL Server 2017.
The application of this idea as to relate to the given example is when concatenating data within a single property (e.g., specific column set). Which one from the given example do offer much performance efficient in terms of considering the constantly growing database.
Other than this, can somebody give a set of queries in order to avoid anti-pattern?
I want to deep dive into anti-pattern scheme, for me to really understand how it really affects queries.
UPDATE:
DECLARE @compile_table_str_agg TABLE( --denormalizing data through comma-delimited data compilation
user_id INT NOT NULL,
username NVARCHAR(100) NOT NULL,
email NVARCHAR(100) NOT NULL,
status VARCHAR(50),
feature_names VARCHAR(100) NOT NULL
);
DECLARE @compile_table_json TABLE( --denormalizing data through json-structure data compilation
user_id INT NOT NULL,
username NVARCHAR(100) NOT NULL,
email NVARCHAR(100) NOT NULL,
status VARCHAR(50),
feature_names VARCHAR(100) NOT NULL
);
INSERT INTO @compile_table_str_agg
SELECT
u.id AS user_id,
u.username,
u.email,
u.status,
STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;
INSERT INTO @compile_table_json
SELECT
feature_data.user_id,
feature_data.username,
feature_data.email,
feature_data.status,
N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT
u.id AS user_id,
u.username,
u.email,
u.status,
STRING_AGG('"' + f.name, + '", ') feature_names
--ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;
-- Retrieving the compiled STRING_AGG data
SELECT
user_id,
username,
email,
status,
ca.*
FROM
@compile_table_str_agg str_agg1
CROSS APPLY(
SELECT
com_delimited.*
FROM
STRING_SPLIT(
feature_names, ',') com_delimited
) ca;
-- Retrieving the compiled JSON structure data
SELECT
user_id,
username,
email,
status,
sa.ft_values
FROM
@compile_table_json json1
CROSS APPLY
OPENJSON(json1.feature_names) WITH(
feature_data NVARCHAR(MAX) '$.feature_data' AS JSON
) ca
CROSS APPLY OPENJSON(ca.feature_data) WITH (ft_values NVARCHAR(25) '$') sa;
And the UPDATE, SELECT query outputs are:
CONCLUSION: Both offer the same output, but which one offers more performance or maintainability efficiency even as the database gets larger?
JSON QUERY REFERENCE:
DECLARE @json NVARCHAR(MAX);
SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[0].town', 'Philippines');
SELECT modifiedJson = @json;
DECLARE @json2 NVARCHAR(MAX);
SET @json2 = N'[
{"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
{"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';
SELECT *
FROM OPENJSON(@json2) WITH (
id INT 'strict $.id',
firstName NVARCHAR(50) '$.info.name',
lastName NVARCHAR(50) '$.info.surname',
age INT,
dateOfBirth DATETIME2 '$.dob'
);
DECLARE @json3 NVARCHAR(MAX);
SET @json3 = N'[
{"id": 3, "info": {"name": "John", "surname": "Smith"}, "age": 25},
{"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"},
{"id": 1, "info": {"name": "DevQt", "surname": "PH", "skills": ["Dart", "Java", "C#", "VB", "Javascript", "SQL"]}, "age": 26, "dob": "2005-11-04T12:00:00"}
]';
SELECT id,
firstName,
lastName,
age,
dateOfBirth,
skill
FROM OPENJSON(@json3) WITH (
id INT 'strict $.id',
firstName NVARCHAR(50) '$.info.name',
lastName NVARCHAR(50) '$.info.surname',
age INT,
dateOfBirth DATETIME2 '$.dob',
skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(15) '$')
ORDER BY id;
DECLARE @jsonVariable NVARCHAR(MAX);
SET @jsonVariable = N'[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]';
-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
Number VARCHAR(200) N'$.Order.Number',
Date DATETIME N'$.Order.Date',
Customer VARCHAR(200) N'$.AccountNumber',
Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;
And the JSON query outputs are:
Additional explanations: I've included the reference in handling JSON for the others to see my perception of embedding JSON with relational databases in SQL Server.
Original reference: JSON data in SQL Server
