TSQL transform JSON Array in each row

Question

I have a table with a JSON column with following content in each row:

{"DoublesIds":[{"RecordId":415070},{"RecordId":4370908}]}

For each RecordId there is a TransactionId in a separate Record table:

RecordId	TransactionId
415070	12357
4370908	12354

I'd like to transform each JSON object so that it contains JSON array like that:

{
"DoublesIds":
[
{"RecordId":415070, TransactionId: 12357},
{"RecordId":4370908, TransactionId: 12354}
]
}

Answer 1

This query can be solved by some OPENJSON together with a FOR JSON PATH:

DECLARE @data TABLE (id int, js nvarchar(max))

INSERT INTO @data 
VALUES (1, N'{"DoublesIds":[{"RecordId":415070},{"RecordId":4370908}]}')
,   (2, N'{"DoublesIds":[{"RecordId":415071},{"RecordId":437091},{"RecordId":437092}]}')

DECLARE @transactions TABLE (recordid INT, TransactionId INT)
INSERT INTO @transactions
VALUES  (415070, 12357)
,   (4370908, 12354)
,   (415071, 98765)
,   (437091, 54321)
,   (437092, 12345)

-- Output
SELECT  *
,   (
    SELECT  od.RecordId, t.TransactionId
    FROM    OPENJSON(d.js, '$.DoublesIds') WITH (RecordId INT) od
    INNER JOIN @transactions t
        ON  t.recordid = od.RecordId
    ORDER BY od.RecordId
    FOR JSON PATH, ROOT('DoublesIds')
) AS jsNew
FROM    @data d

In the correlated subquery, you open up the js-column to get the RecordIds, and then join the transaction-table on them. Finally, you rebuild your json as needed.

One little wrinkle is the sorting of recordIds in the new json. If you want to respect the original sort, it takes a bit more work, but you should be able to figure it out if you read the OPENJSON documentation

Output:

id	js	jsNew
1	{"DoublesIds":[{"RecordId":415070},{"RecordId":4370908}]}	{"DoublesIds":[{"RecordId":415070,"TransactionId":12357},{"RecordId":4370908,"TransactionId":12354}]}
2	{"DoublesIds":[{"RecordId":415071},{"RecordId":437091},{"RecordId":437092}]}	{"DoublesIds":[{"RecordId":415071,"TransactionId":98765},{"RecordId":437091,"TransactionId":54321},{"RecordId":437092,"TransactionId":12345}]}