Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

FOR JSON Path in Fabric Warehouse



I have noticed that the FOR JSON statement is not supported in Fabric Synapse, and was wondering if there is any alternative to it?

I am working on a Stored Procedure that will be used in a pipeline in Data Factory, and one of the things I want the SP to do is to pass several parameters to the next pipeline that will be executed. I have the parameters in table format and have previously in SQL Server turned them into JSON-format with FOR JSON Path-statement, but this is not supported in Fabric. That is why I was wondering if there is a similar statement that is available in Fabric, and if not, what are some possible work-arounds?



Advocate I
Advocate I

Could we please get a reply on this topic? It seems like the confusing "SELECT - FOR (except JSON)" has just been removed from the documentation without any notice.

Frequent Visitor

We seem to have been a bit forgotten! Hopefully we'll get an answer soon

Hi @Etc 


I have checked with the internal team.

Apologies for the issue and delay in the response. JSON types and JSON functions are not supported in warehouse in Fabric currently. This is not supported.

But you could share your feedback on our feedback link which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Feedback Link : Home (


Thank you.

Advocate I
Advocate I

You could potentially look att the STRING_AGG function and combine it with CONCAT. But it will only work for very simple json-objects. I tried that with some success in Fabric.


with source_table as (
select 5 as test1, 'something' as test2
union all
select 6, 'something else'
,r AS (
    select test1, test2, ROW_NUMBER() OVER (ORDER BY test1, test2) as RowNum
    from source_table 

,build_json as (
select concat('{"test1":',test1,',"test2":"',test2,'","RowNum":',RowNum,'}') as json_object
from r

select concat('[',STRING_AGG(json_object,','),']') as json_done
from build_json


However I'm also interested in this topic, because the documentation kind off says that it should work? Under limitations it says: "SELECT - FOR (except JSON)"



I have also noticed that the error message is different when trying to run "FOR JSON":

Msg 40598, Level 15, State 7, Line 1
'FOR' clause is not supported in this version of Synapse SQL.


Compared to "FOR XML":

Msg 15868, Level 16, State 12, Line 14
FOR XML is not supported.

Frequent Visitor

Hi FilipO,


This is the work-around I also landed on, but as you say, it also confused me with that exact statement in the documentation ("SELECT - FOR (except JSON)") - it sounds like it should work.


Additionally, as you mention, the string_agg() statement can be used, but it will only work for simple JSON objects. 

Community Support
Community Support

Hi @Etc 


Thanks for using Fabric Community and reporting this.


Apologies for the issue you have been facing. 

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.


Appreciate your patience.

Hi @Etc 


Could you please share more detail about your ask and also please explain this statement more clear - "SQL server into JSON-format with FOR JSON Path-statement"?



Of course, let me try to clarify my question.

Basically I want to use a table I have to create a JSON string that contains the values in the table. In Azure SQL Database, it is possible to use "JSON PATH" statement which does this. Below is an example of a code snippet that does this in Azure SQL Database:

DECLARE @sourceTable TABLE (
    test1 int,
    test2 varchar(255)

DECLARE @destinationTable TABLE (testRow NVARCHAR(MAX));

INSERT @sourceTable  (test1, test2)
VALUES (5, 'something'),
       (6, 'something else');

    SELECT test1, test2, ROW_NUMBER() OVER (ORDER BY test1, test2) AS RowNum
    FROM @sourceTable 
INSERT @destinationTable (testRow)
FROM r r2;

SELECT testRow FROM @destinationTable;


This will result in two JSON-formatted strings {"test1":5,"test2":"something} and {"test1":6,"test":"something else"}

My question is specifically the statement "FOR JSON"-statement in the code snippet, which is not supported in Fabric Warehouses - is there any alternatives to this in Fabric or any possible work-arounds to get the JSON-formatted string from the table?


Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.