Forum Discussion

Etc's avatar
Etc
Frequent Visitor
2 years ago

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?

 

 

data engineering
Data Integration
data warehouse

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Etc
      Frequent Visitor
      2 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Etc
      Frequent Visitor
      2 years ago

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

      • v-cboorla-msft's avatar
        v-cboorla-msft
        Microsoft Employee
        2 years ago

        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.

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    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's avatar
      v-cboorla-msft
      Microsoft Employee
      2 years ago

      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's avatar
        Etc
        Frequent Visitor
        2 years ago

        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?