Migrate columns to JSON in SQL

I have a table (Table A) that looks like this:

Id (int)  
Quantity (int)   
Clicks (int?)  
Opens (int?)  
VendorName (string) 

I want to convert it to a table (Table B) like this

Id (int)  
JsonData (Json that has the 4 fields above)

How do you insert/transform into a json column? Or do I just have to construct the json string myself?

Additionally, does the nullableness of the int columns affect creation? Are clicks for example not included or do they do something like this {"clicks": null }?

I read through Microsoft’s documentation about JSON, but I didn’t see any reference to inserting from SQL columns. Only from other JSON data.

Answer

You simply need FOR JSON PATH with WITHOUT_ARRAY_WRAPPER and INCLUDE_NULL_VALUES options:

Table:

CREATE TABLE TableA (
   Id int,  
   Quantity int,   
   Clicks int,  
   Opens int,  
   VendorName varchar(100)
)
INSERT INTO TableA (Id, Quantity, Clicks, Opens, VendorName)
VALUES
   (1, 100, 100, 100, 'Vendor1'),
   (2, 200, NULL, 200, 'Vendor2')

Statement:

SELECT
   Id,
   (
   SELECT Quantity, Clicks, Opens, VendorName 
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
   ) AS JsonData
-- INTO TableB
FROM TableA   

Result:

Id  JsonData
1   {"Quantity":100,"Clicks":100,"Opens":100,"VendorName":"Vendor1"}
2   {"Quantity":200,"Clicks":null,"Opens":200,"VendorName":"Vendor2"}