I am writing an EF migration to update data in a JSON column, names are being simplified into a single field. As a first step I want to populate the new field. This is what I have come up with having looked at many SO questions and answers (copilot was little help)
DECLARE @t AS TABLE
(
id int IDENTITY(1, 1),
details nvarchar(max)
)
INSERT INTO @t (details)
VALUES (
N'{
"Contacts":
[
{"Id": 1, "FirstName": "John", "LastName": "Doe"},
{"Id": 2, "FirstName": "Peter", "LastName": "Pan"}
]
}')
SELECT
-- will replace with UPDATE
JSON_MODIFY(
details,
'$.Contacts',
JSON_QUERY((
SELECT '[' + STRING_AGG(updated_json, ',') + ']'
FROM (
SELECT JSON_MODIFY(
c.[value],
'$.Name',
CONCAT(ISNULL(NULLIF(JSON_VALUE(value, '$.LastName'), ''), ''), ', ', ISNULL(NULLIF(JSON_VALUE(value, '$.FirstName'), ''), ''))
) as updated_json
FROM OPENJSON(Details, '$.Contacts') c
) t
))
)
FROM @t
This returns the following output:
{
"Contacts":
[
{"Id": 1, "FirstName": "John", "LastName": "Doe", "Name": "Doe, John"},
{"Id": 2, "FirstName": "Peter", "LastName": "Pan", "Name": "Pan, Peter"}
]
}
Is there a better way to write this without the string manipulation adding the [ and ] with STRING_AGG?
All my attempts added a new node [{"updated_json":{"Id": ... e.g.
SELECT
-- this code returns the "updated_json" extra node
JSON_MODIFY(
details,
'$.Contacts',
JSON_QUERY(
(SELECT
JSON_MODIFY(value, '$.FirstName',
CONCAT(ISNULL(NULLIF(JSON_VALUE(value, '$.LastName'), ''), ''), ', ', ISNULL(NULLIF(JSON_VALUE(value, '$.FirstName'), ''), ''))
) as updated_json
FROM OPENJSON(Details, '$.Contacts')
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
)
FROM @t
SELECT id, details = JSON_MODIFY(details, '$.Contacts', (SELECT Id, FirstName, LastName, CONCAT(LastName, ', ', FirstName) AS Name FROM OPENJSON(details, '$.Contacts') WITH (Id int '$.Id', FirstName nvarchar(max) '$.FirstName', LastName nvarchar(max) '$.LastName') FOR JSON PATH)) FROM @t.