When I use left join with for json path I get an error:
Property 'product_id' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.
from this query:
select top 1
1 as [foo.bar],
p.*, cp.*
from product p
left JOIN company_product cp on cp.product_id = p.product_id
for json path
when I change from for json path to for json auto it's not throw error but the result is not output as expected:
[{ "foo.bar": 1, "product_id": 751, ... "cp": [{ "product_id": 751, "company_id": 273, .. }]
and it's make another problem when I move 1 as [foo.bar] to the end of the select:
select top 1 p., cp., 1 as [foo.bar] from product p left JOIN company_product cp on cp.product_id = p.product_id for json auto
The foo.bar is inside cp object. and it's not as expected:
[{ "product_id": 751, ... "cp": [{ "product_id": 751, "company_id": 273, "foo.bar": 1, ..}]
Instead should be:
[{ "product_id": 751, ... "cp": [{ "product_id": 751, "company_id": 273, ..}], "foo.bar": 1, ..}]
So I have those problems when I use for json auto. any idea if there is a way to use left join with for path or solve this issues that for auto makes?
