Forum Discussion
FOR JSON Path in Fabric Warehouse
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.
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.
