sql output json structure-CodePudding

I need to get the following output about the sql query:

{
    "records": [
        {
            "attributes": {
                "type": "customer"
            },
            "name": "test 1",
            "email": "[email protected]"
        },
        {
            "attributes": {
                "type": "customer"
            },
            "name": "test 2",
            "email": "[email protected]"
        }
    ]
}

my attempt:

DECLARE @ttCustomer table (cname nvarchar(50),
                           email nvarchar(250));
INSERT @ttCustomer
VALUES ('test 1', '[email protected]');
INSERT @ttCustomer
VALUES ('test 2', '[email protected]');
SELECT 'customer' AS [records.attributes.type],
       cname AS [records.name],
       email AS [records.email]
FROM @ttCustomer
FOR JSON PATH;

almost perfect output result

[
    {
        "records": {
            "attributes": {
                "type": "customer"
            },
            "name": "test 1",
            "email": "[email protected]"
        }
    },
    {
        "records": {
            "attributes": {
                "type": "customer"
            },
            "name": "test 2",
            "email": "[email protected]"
        }
    }
]

CodePudding user response：

Just define your ROOT:

DECLARE @ttCustomer table (cname nvarchar(50),
                           email nvarchar(250));
INSERT @ttCustomer
VALUES ('test 1', '[email protected]');
INSERT @ttCustomer
VALUES ('test 2', '[email protected]');
SELECT 'customer' AS [attributes.type],
       cname AS [name],
       email AS [email]
FROM @ttCustomer
FOR JSON PATH, ROOT('records');

Outputs:

{
    "records": [
        {
            "attributes": {
                "type": "customer"
            },
            "name": "test 1",
            "email": "[email protected]"
        },
        {
            "attributes": {
                "type": "customer"
            },
            "name": "test 2",
            "email": "[email protected]"
        }
    ]
}