SQL Server Migrate columns to JSON in SQL

j1dl9f46 于 2023-04-28 发布在其他

关注(0)|答案(2)|浏览(202)

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.

sql-server

来源：https://stackoverflow.com/questions/63675102/migrate-columns-to-json-in-sql

2条答案

按热度按时间

dldeef671#

For SQL Server 2016+ you may try a combination of FOR JSON PATH with WITHOUT_ARRAY_WRAPPER and INCLUDE_NULL_VALUES .

Starting from SQL Server 2022, you may use JSON_OBJECT() function.

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 for SQL Server 2016+:

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

Statement for SQL Server 2022+:

SELECT
   Id,
   JSON_OBJECT(
     'Quantity': Quantity, 
     'Clicks': Clicks, 
     'Opens': Opens, 
     'VendorName': VendorName
     NULL ON NULL
   ) AS JsonData
FROM TableA

Result:

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

赞(0）回复(0）举报 2023-04-28

ddrv8njm2#

Something like this

drop table if exists #TableB;
create table #TableB(
  Id            int unique not null,
  JsonData      nvarchar(max) not null);
go

insert #TableB(Id, JsonData) 
select t.Id, (select ta.Quantity, ta.Clicks, ta.Opens, ta.VendorName
              from #TableA ta
              where t.Id=ta.Id
              for json path, root('JsonArray')) 
from #TableA t
group by t.Id;

select tb.Id, oj.Quantity, oj.clicks, oj.Opens, oj.VendorName
from #TableB tb
     cross apply openjson(tb.JsonData, '$.JsonArray') with (Quantity       int,
                                                            Clicks         int,
                                                            Opens          int,
                                                            VendorName     varchar(200)) oj;

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server Migrate columns to JSON in SQL

2条答案

相关问题

热门标签

最新问答