CODEWITHSUNDEEP
sqljsonsql-servert-sql
pdaniels0013
pdaniels0013

Reputation: 411

Convert a sub-selection of a result set in T-SQL into JSON using "for json auto"

I have a trigger that fires on insert to my table. I want to be able to serialize each row in the inserted result set and insert it as a "JsonValue" column in another table, as well as some other values into a new table. Currently the trigger looks something like this:

create trigger dbo.InsertContactAudit
on Contact
   after insert
as
begin
    set nocount on;
    insert into ContactAudit (
        JsonMessage, EventType, ContactId, ProjectId, CorrelationId
    )
    select 'entire row serialized to json', 'create', inserted.ContactId, inserted.ProjectId, inserted.CorrelationId
    from Contact inserted;

The piece I am having trouble with is the 'entire row to json here'. In a similar situation I could do something like:

select column1, column2, column3 from table for json auto;

and that would give me something similar to { "column1": "value 1", "column2", "value2", ... }

Is there a way to get the behavior I want? Serialize a whole row into json while still inserting into other columns, within the trigger?

Upvotes: 0

Views: 394

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8819

Is there a particular reason you want to use JSON AUTO?

To achieve the JSON format you're asking for, with the set of columns for each row in its own JSON blob without the surrounding [] characters, you could do something like this...

use master
go
if exists(select * from sys.databases where name='StackOverflow')
    drop database StackOverflow
go
create database StackOverflow
go
use StackOverflow;
create table dbo.Contact (
    ContactId int not null identity,
    ProjectId int,
    CorrelationId int,
    Column1 nvarchar(20),
    Column2 nvarchar(20),
    Column3 nvarchar(20)
);
create table dbo.ContactAudit (
    JsonMessage nvarchar(max),
    EventType nvarchar(20),
    ContactId int not null,
    ProjectId int,
    CorrelationId int
);
go
create trigger dbo.InsertContactAudit on dbo.Contact
after insert as
begin
    set nocount on;
    insert into dbo.ContactAudit (JsonMessage, EventType, ContactId, ProjectId, CorrelationId)
        select (
        select ContactId, ProjectId, CorrelationId, Column1, Column2, Column3
        for json path, without_array_wrapper
        ), 'create', ContactId, ProjectId, CorrelationId
        from inserted;
end
go
insert Contact (ProjectId, CorrelationId, Column1, Column2, Column3)
    values (null, null, null, null, null);
insert Contact (ProjectId, CorrelationId, Column1, Column2, Column3)
    values (1, 2, 'Foo', 'Bar', 'Baz');
select * from dbo.ContactAudit;
go

Which would record the following into dbo.ContactAudit...

JsonMessage                                                                                     EventType            ContactId   ProjectId   CorrelationId
----------------------------------------------------------------------------------------------- -------------------- ----------- ----------- -------------
{"ContactId":1}                                                                                 create               1           NULL        NULL
{"ContactId":2,"ProjectId":1,"CorrelationId":2,"Column1":"Foo","Column2":"Bar","Column3":"Baz"} create               2           1           2

Upvotes: 2

Related Questions