question

RajD-9527 avatar image
0 Votes"
RajD-9527 asked RajD-9527 commented

Azure Synapse Analytics support for json

Hi, I am using Azure Synapse Analytics to output a JSON document from the results of a table. The below query works in SQL Server. Could you please guide to work around using Synapse Analytics pool.

   DROP TABLE IF EXISTS #Data
 CREATE TABLE #Data ([Id] int, [Description] varchar(150))
 INSERT INTO #Data VALUES (1, 'Test 1'), (2, 'Test 2'), (3, 'Test 3')
 SELECT (SELECT [Id],
     [Description]
           FROM #Data
     FOR JSON PATH, root('data'))

JSON

 {
    "results":[
       {
          "Id":1,
          "Description":"Test 1"
       },
       {
          "Id":2,
          "Description":"Test 2"
       },
       {
          "Id":3,
          "Description":"Test 3"
       }
    ]
 }

What other options are available to output JSON?

Thank you in advance


azure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, I tried using the FOR XML as well and that is not supported either.

Thank you.

0 Votes 0 ·

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered RajD-9527 commented

Hello @RajD-9527,

Thanks for the question and using MS Q&A platform.

Currently, both JSON and XML are not supported in Azure Synapse Analytics.

125815-image.png

For more details, refer to JSON data in SQL Server and FOR XML (SQL Server)

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.


image.png (12.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PRADEEPCHEEKATLA-MSFT Thank you for your response. If we were to create json documents from Azure Synapse database what work around options are available.

Regards

0 Votes 0 ·