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

Question

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.

If you are using sql server 2016 have a look at the FOR JSON command — MotKohn
– MotKohn, Commented Jul 19, 2017 at 17:45
I'm aware of for json path, however my question is, can I avoid unpivoting then creating a view etc and straight away create the output json of the example table above using any tool that can satisfy my question. — Sharvil Popli
– Sharvil Popli, Commented Jul 19, 2017 at 19:40
You can do any type of SELECT query and save it to json format using for json. There are also some commands that manipulate JSON data directly. I never used them so no experience. — MotKohn
– MotKohn, Commented Jul 20, 2017 at 16:31

AlwaysLearning · Accepted Answer · 2019-05-09 01:16:33Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related