I’ve got a table in my database that contains a bunch of JSON paths and the values at those paths. I want to take the data in that table and construct a JSON object out of it. I’m more of a .NET guy and my go to approach with that kind of background would just be to run a WHILE loop and call JSON_MODIFY()
for each row in the table, but I’m posting here in hopes that someone has a more elegant/efficient solution that’d be more appropriate for SQL. It seems like OPENJSON()
might be able to help me out here, but I’m not quite sure how to use it to here.
Table
CREATE TABLE [dbo].[JsonData]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
[Path] VARCHAR(MAX) NOT NULL,
[Value] VARCHAR(MAX) NOT NULL
)
Not sure if the empty object value with Id #1 and #6 are necessary for all solutions to this. But with the WHILE loop at least it ensured we got the base object created so that JSON_MODIFY()
could set the properties on it.
Sample Data
INSERT INTO [dbo].[JsonData] ([Path], [Value])
VALUES
('FullName', '{}'),
('FullName.FirstName', 'Robert'),
('FullName.MiddleName', 'John'),
('FullName.LastName', 'Downey'),
('FullName.Suffix', 'Jr.'),
('CurrentDate', '{}'),
('CurrentDate.Month', 'February'),
('CurrentDate.Day', '22'),
('CurrentDate.Year', '2024')
Desired Outcome
{
"FullName": {
"FirstName": "Robert",
"MiddleName": "John",
"LastName": "Downey",
"Suffix": "Jr."
},
"CurrentDate": {
"Month": "February",
"Day": 22,
"Year": 2024
}
}
I appreciate any advice!
My Rudimentary WHILE loop solution
DECLARE @json VARCHAR(MAX) = '{}'
DECLARE @currentId INT
DECLARE @finalId INT
SELECT
@currentId = MIN(Id),
@finalId = MAX(Id)
FROM
JsonData
WHILE @currentId <= @finalId
BEGIN
DECLARE @path VARCHAR(MAX)
DECLARE @value VARCHAR(MAX)
SELECT
@path = j.[Path],
@value = j.[Value]
FROM
JsonData j
WHERE
j.Id = @currentId
DECLARE @fullPath varchar(max) = FORMATMESSAGE('$.%s', @path)
IF ISJSON(@value) = 1 -- Not ideal, but took a hacky route here to make this work as an example
SET @json = JSON_MODIFY(@json, @fullPath, JSON_QUERY(@value))
ELSE IF TRY_CONVERT(INT, @value) IS NOT NULL
SET @json = JSON_MODIFY(@json, @fullPath, CAST(@value as INT))
ELSE
SET @json = JSON_MODIFY(@json, @fullPath, @value)
SET @currentId = @currentId + 1
END
SQL Version
Microsoft SQL Azure (RTM) – 12.0.2000.8 Dec 6 2023 08:32:07 Copyright (C) 2022 Microsoft Corporation
Edit
As Dale mentioned, here’s the business requirements
- Should be able to handle any valid JSON path (i.e. anything that could be passed into
JSON_MODIFY
). We have flexibility on how to format the incoming paths if that is helpful somehow - Should handle duplicate paths in the table and insert the latest data (higher ID = newer data) into the final JSON object
- Data should be inserted into the JSON object in its ‘native’ form. I’m sure there’s a better way to phrase this, but as an example I mean that if ’12’ is in the
Value
column, it should go into the JSON as 12 and not ’12’. We can add a column for data type to the JsonData table and populate it with the correct data type if that’s helpful. I edited in a rudimentary way to do this in my example SQL although I expect that wouldn’t be the best long term solution - Should be able to handle a decent amount of data. In our use case, I think we’re expecting this table to have between 100 and 500 rows of applicable data, with the usual number being ~200. I think this might rule out the recursive approach
Sorry for not clarifying on all of these sooner
2
Answers
Here is one option using Dynamic SQL
Results
Update. Just noticed the Latest Path requirement
You could use the following recursive CTE.
OUTER APPLY
the following:TOP (1)
automatically.We can’t do this as it’s not allowed in a rCTE.
ISJSON
to check for those{}
values. They aren’t strictly necessary if you have child properties of those, but you may need them if you don’t have those.db<>fiddle