CODEWITHSUNDEEP
sqlsql-serverjsonazure-cosmosdb
Sharvil Popli
Sharvil Popli

Reputation: 158

Convert table in SQL server into JSON string for migration into DocumentDB

I have a table called DimCompany in SQL Server like so:

+----+---------+--------+
| id | Company | Budget |
+----+---------+--------+
|  1 | abc     |    111 |
|  2 | def     |    444 |
+----+---------+--------+

I would like to convert this table into a json file like so:

{
"DimCompany":{
    "id":1,
    "companydetails": [{
            "columnid": "1",
            "columnfieldname": "Company",
            "columnfieldvalue: "abc"
            }
            {
            "columnid": "2",
            "columnfieldname": "Budget",
            "columnfieldvalue: "111"
            }]
    }
},
{
"DimCompany":{
    "id":2,
    "companydetails": [{
            "columnid": "1",
            "columnfieldname": "Company",
            "columnfieldvalue: "def"
            }
            {
            "columnid": "2",
            "columnfieldname": "Budget",
            "columnfieldvalue: "444"
            }]
    }
}

where columnid is a value from sys.columns against the column field name. I've tried doing this by unpivoting the table and joining sys.columns on fieldname where sys.objects.name=DimCompany and putting this in a view, then querying on the view to get json output for migration into DocumentDB.

However I would like to not use unpivot and just directly form a query to get desired output.

I'm just curious whether this is possible in SQL server or in any other tool.

Upvotes: 1

Views: 1218

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8809

Without using UNPIVOT and doing it yourself, the following SQL:

if object_id(N'dbo.DimCompany') is not null drop table dbo.DimCompany;
create table dbo.DimCompany (
    id int not null identity(1,1),
    Company nvarchar(50) not null,
    Budget float not null
);
insert dbo.DimCompany (Company, Budget) values
    ('abc', 111),
    ('def', 444);
go
select  id as 'DimCompany.id',
    (
        select columnid=cast(sc.column_id as nvarchar), columnfieldname, columnfieldvalue
        from (
            select  N'Company', Company from dbo.DimCompany DC2 where DC2.id = DC1.id
            union
            select  N'Budget', cast(Budget as nvarchar) from dbo.DimCompany DC2 where DC2.id = DC1.id
        ) keyValues (columnfieldname, columnfieldvalue)
        join sys.columns sc on sc.object_id=object_id(N'dbo.DimCompany') and sc.name=columnfieldname
        for json path
    ) as 'DimCompany.companydetails'    
from    dbo.DimCompany DC1
for json path, without_array_wrapper;

Produces the following JSON as per your example:

{
    "DimCompany": {
        "id": 1,
        "companydetails": [
            {
                "columnid": "2",
                "columnfieldname": "Company",
                "columnfieldvalue": "abc"
            },
            {
                "columnid": "3",
                "columnfieldname": "Budget",
                "columnfieldvalue": "111"
            }
        ]
    }
},
{
    "DimCompany": {
        "id": 2,
        "companydetails": [
            {
                "columnid": "2",
                "columnfieldname": "Company",
                "columnfieldvalue": "def"
            },
            {
                "columnid": "3",
                "columnfieldname": "Budget",
                "columnfieldvalue": "444"
            }
        ]
    }
}

Things to note:

  • The sys.columns columnid values start at 1 for the dbo.DimCompany.id column. Subtract 1 before casting if that's a requirement.
  • Using without_array_wrapper removes the surrounding [] characters, per your example, but isn't really valid JSON as a result.
  • I doubt this would be scalable for tables with large numbers of columns.

Upvotes: 1

Related Questions