FOR JSON Path in Fabric Warehouse

[Etc]

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?

[FilipO]

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]

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

[v-cboorla-msft]

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]

I have now created an idea. Please vote! https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=ca025a38-228e-ee11-a81c-6045bdb0f8f5

[FilipO]

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]

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]

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.

[v-cboorla-msft]

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

[Etc]

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?