Saving T-SQL query with subqueries details as Json file using Powershell

Question

I have SQL Server (2016 / 2019) query with sub queries as json.

The complete result should be saved as Json file using Powershell. It works well if there is no sub-select (with FOR JSON PATH).

But the result of the subselect is only saved as string, also the the main description "list" is missing.

Result file should look like this:

{
    "List": [{
            "MainId": 1,
            "Name": "A",
            "sub": [{
                    "SubId": 1,
                    "Description": "a",
                    "detail": [{
                            "DetailId": 1,
                            "Details": "detail1"
                        }
                    ]
                }, {
                    "SubId": 2,
                    "Description": "aa"
                }
            ]
        }, {
            "MainId": 2,
            "Name": "B",
            "sub": [{
                    "SubId": 3,
                    "Description": "b",
                    "detail": [{
                            "DetailId": 2,
                            "Details": "detail2"
                        }, {
                            "DetailId": 3,
                            "Details": "detail3"
                        }
                    ]
                }
            ]
        }
    ]
}

Powershell Script:

# Define your SQL query
$sqlQuery = @"
DECLARE @MainList TABLE (id INT, name NVARCHAR(50))
DECLARE @SubList TABLE (id INT, mainId INT, description NVARCHAR(50))
DECLARE @DetailList TABLE (id INT, subId INT, details NVARCHAR(50))

INSERT INTO @Mainlist VALUES (1, 'A'), (2, 'B')
INSERT INTO @SubList VALUES (1, 1, 'a'), (2, 1, 'aa'), (3, 2, 'b')
INSERT INTO @DetailList VALUES (1, 1, 'detail1'), (2, 3, 'detail2'), (3, 3, 'detail3')


SELECT
    m.id AS MainId,
    m.name AS Name,
    (SELECT
        s.id AS SubId,
        s.description AS Description,
        (SELECT
            d.id AS DetailId,
            d.details AS Details
        FROM
            @DetailList d
        WHERE
            d.subId = s.id
        FOR JSON PATH
        ) AS detail
    FROM @SubList s WHERE s.mainId = m.id FOR JSON PATH
    ) AS sub
FROM @MainList AS m
"@

# Execute the SQL query using Invoke-Sqlcmd
$result = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourServerName" -Database "YourDatabaseName" -Username "YourUsername" -Password "YourPassword"
 

# Convert the result to JSON
$jsonResult = $result | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json  -Depth 3  
 

# Define the path to save the JSON file
$jsonFilePath = "C:\OutputFile.json"
 

# Save the JSON result to a file
$jsonResult | Out-File -FilePath $jsonFilePath -Encoding UTF8

Instead the result file looks like this:

[
    {
        "MainId":  1,
        "Name":  "A",
        "sub":  "[{\"SubId\":1,\"Description\":\"a\",\"detail\":[{\"DetailId\":1,\"Details\":\"detail1\"}]},{\"SubId\":2,\"Description\":\"aa\"}]"
    },
    {
        "MainId":  2,
        "Name":  "B",
        "sub":  "[{\"SubId\":3,\"Description\":\"b\",\"detail\":[{\"DetailId\":2,\"Details\":\"detail2\"},{\"DetailId\":3,\"Details\":\"detail3\"}]}]"
    }
]

Please help me solve this problem. Is there a way to change the T-SQL query to get the right output or change the Powershell script to get the output?

Thanks

Answer 1

Put the whole query in an outer FOR JSON PATH, then you only get one big JSON value. Save that to a file.

$sqlQuery = @"
DECLARE @MainList TABLE (id INT, name NVARCHAR(50))
DECLARE @SubList TABLE (id INT, mainId INT, description NVARCHAR(50))
DECLARE @DetailList TABLE (id INT, subId INT, details NVARCHAR(50))

INSERT INTO @Mainlist VALUES (1, 'A'), (2, 'B')
INSERT INTO @SubList VALUES (1, 1, 'a'), (2, 1, 'aa'), (3, 2, 'b')
INSERT INTO @DetailList VALUES (1, 1, 'detail1'), (2, 3, 'detail2'), (3, 3, 'detail3')

SELECT (
  SELECT
    m.id AS MainId,
    m.name AS Name,
    (
      SELECT
        s.id AS SubId,
        s.description AS Description,
        (
          SELECT
            d.id AS DetailId,
            d.details AS Details
          FROM
            @DetailList d
          WHERE
            d.subId = s.id
          FOR JSON PATH
        ) AS detail
      FROM @SubList s
      WHERE s.mainId = m.id
      FOR JSON PATH
    ) AS sub
  FROM @MainList AS m
  FOR JSON PATH, ROOT('List')
);
"@;

# Execute the SQL query using Invoke-Sqlcmd
$result = Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourServerName" -Database "YourDatabaseName" -Username "YourUsername" -Password "YourPassword";
 

# Get first row first column
$jsonResult = $result[0][0];
 

# Define the path to save the JSON file
$jsonFilePath = "C:\OutputFile.json";
 

# Save the JSON result to a file
$jsonResult | Out-File -FilePath $jsonFilePath -Encoding UTF8;