How can we achieve a left join by using subquery in Cosmos DB?

Question

Here I am using the same document in left join as well:

select A.a,A.b,C.c
from Inventory A left join
     (select B.a as id,B.a*100 as c
      from Inventory B
      where condition1 and condition2
     ) as C
     on C.id
     on A.a

How could I achieve this without a left join in the above use-case?

Your query makes no sense even after I have formatted it. Sample data, desired results, and an explanation of the logic would help. — Gordon Linoff, Aug 09 '20 at 15:22
fyi https://stackoverflow.com/questions/49368254/cosmosdb-join-sql-api — frozen, Aug 09 '20 at 15:26

score 1 · Answer 1 · answered Aug 10 '20 at 09:21

1

Cosmos DB only support self-join by now.You can refer to this documentation.Left join is in Cosmos DB Development Team plan,and tentative start for H2 2020.You can track this feature here.

answered Aug 10 '20 at 09:21

Steve Johnson

8,057
1
6
17

score 0 · Answer 2 · answered Aug 09 '20 at 15:29

Assuming that on C.id on A.a is a typo and is really meant to be on C.id = A.a:

select a.a, a.b, c.c
from inventory a 
left join 
(
  select b.a as id, b.a * 100 as c
  from inventory b
  where condition1 and condition2
) as c on c.id = a.a;

equals

select a.a, a.b, 
  (
    select b.a * 100
    from inventory b
    where condition1 and condition2
    and b.a = a.a
  ) as c
from inventory a;

How can we achieve a left join by using subquery in Cosmos DB?

2 Answers2