How to use left join with for json path in mssql?

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?

Answer

As is explained in the documentation, when …you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables… and you …can’t change this format. So, a possible option is to use a subquery:

Tables:

SELECT *
INTO product
FROM (VALUES (751)) v (product_id)
SELECT *
INTO company_product
FROM (VALUES (751, 273), (751, 274)) v (product_id, company_id)

Statement:

SELECT p.*, cp.*
FROM (
   SELECT *, 1 as [foo.bar]
   FROM product 
) p
LEFT JOIN company_product cp on cp.product_id = p.product_id
FOR JSON AUTO

Result:

[
   {
      "product_id":751,
      "foo.bar":1,
      "cp":[
         {"product_id":751, "company_id":273},
         {"product_id":751, "company_id":274}
      ]
   }
]