Skip to main content
cancel
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.

Reply
Etc
Frequent Visitor

FOR JSON Path in Fabric Warehouse

Hi,

 

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?

 

 

9 REPLIES 9
FilipO
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.

Etc
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 (microsoft.com)

 

Thank you.

FilipO
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?

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area 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.

Etc
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. 

v-cboorla-msft
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"?

Hi,

 

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');

WITH r AS (
    SELECT test1, test2, ROW_NUMBER() OVER (ORDER BY test1, test2) AS RowNum
    FROM @sourceTable 
)
INSERT @destinationTable (testRow)
SELECT (SELECT test1, test2 FROM r WHERE RowNum = r2.RowNum FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
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

Announcements
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.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

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

Top Solution Authors