CODEWITHSUNDEEP
jsonsql-server
Blanen
Blanen

Reputation: 782

Flat JSON result from FOR JSON on SQL Server with joined query

I have a query that I need to send to SQL Server and use FOR JSON to get the result back as JSON. This query can include one or more JOINs.

The problem is that I want the result back as a simple flat json with each column as a column, but FOR JSON AUTO automatically nests it (Duh) and FOR JSON PATH is not appropriate because it use the dot-syntax but I want it to completely ignore any of that.

Of course, this could be done on the application server quite easily but I would highly prefer to do this in the database.

Query:

SELECT
    [Product].[ProductName] AS [Product.ProductName], 
    [Category].[CategoryName] AS [Category.CategoryName], 
    [Supplier].[CompanyName] AS [Supplier.CompanyName] 
FROM
    [Products] AS [Product] 
    INNER JOIN [Categories] AS [Category] ON [Category].[CategoryID] = [Product].[CategoryID] 
    LEFT JOIN [Suppliers] AS [Supplier] ON [Supplier].[SupplierID] = [Product].[SupplierID] 
FOR JSON AUTO

Result (For 1):

[
    {
        "Product.ProductName":"Test prod",
        "Category":[
            {
                "Category.CategoryName":"Condiments!!!",
                "Supplier":[
                    {
                        "Supplier.CompanyName":"Exotic Liquids"
                    }
                ]
            }
        ]
    }
]

Result I'm looking for:

[
    {
        "Product.ProductName":"Test Prod",
        "Category.CategoryName":"Condiments!!!",
        "Supplier.CompanyName":"Exotic Liquids"
    }
]

Upvotes: 2

Views: 2081

Answers (2)

Santiago
Santiago

Reputation: 2320

Another way to do it only in 1 to 1 relationship:

SELECT
    [Product].[ProductName] AS [Product.ProductName], 
    (SELECT [Category].[CategoryName] FROM [Categories] WHERE [Category].[CategoryID] = [Product].[CategoryID]) AS [Category.CategoryName], 
    (SELECT [Supplier].[CompanyName] FROM [Suppliers] WHERE [Supplier].[SupplierID] = [Product].[SupplierID]) AS [Supplier.CompanyName] 
FROM
    [Products] AS [Product] 
FOR JSON AUTO

Upvotes: 2

Blanen
Blanen

Reputation: 782

Working solution:

WITH test_CTE
AS
(
    SELECT 
        [Product].[ProductName] AS [Product.ProductName],
        [Category].[CategoryName] AS [Category.CategoryName],
        [Supplier].[CompanyName] AS [Supplier.CompanyName] 
    FROM 
        [Products] AS [Product] 
        INNER JOIN [Categories] AS [Category] ON [Category].[CategoryID] = [Product].[CategoryID] 
        LEFT JOIN [Suppliers] AS [Supplier] ON [Supplier].[SupplierID] = [Product].[SupplierID]
)
SELECT * FROM test_CTE FOR JSON AUTO

Upvotes: 2

Related Questions