none
LEFT JOIN in Azure CosmosDB SQL API RRS feed

  • Question

  • I have used JOIN in Azure Cosmos DB collection using SQL API to query documents.

    I have two contact documents, one is with property Address and another one is without address.

    I need to get the address list of all the persons(including the persons who is not having any address). I have used the below query to do this. But it gives the person list who is having address.

    Is there any way to do LEFT JOIN?

    Query:

    SELECT base.FirstName, base.LastName, Address.City FROM ContactPerson base JOIN Address IN base.Address

    Sample Document:

    [
      {
        "FirstName": "Saravana",
        "LastName": "Kumar",
        "Address": [
          {
            "City": "aaaa"
          },
          {
            "City": "bbbb"
          }
        ]
      },
      {
        "FirstName": "Jayanth",
        "LastName": "T"
      }
    ]

    Expected output:

    [
      {
        "FirstName": "Saravana",
        "LastName": "Kumar",
        "City": "aaa"
      },
      {
        "FirstName": "Saravana",
        "LastName": "Kumar",
        "City": "bbbb"
      },
      {
        "FirstName": "Jayanth",
        "LastName": "T"
      }
    ]

    Actual Output:

    [
      {
        "FirstName": "Saravana",
        "LastName": "Kumar",
        "City": "bbbb"
      },
      {
        "FirstName": "Saravana",
        "LastName": "Kumar",
        "City": "bbbb"
      }
    ]



    Thursday, September 19, 2019 12:49 PM

All replies

  • The SQL API in Cosmos DB is not an ANSI SQL Standard and does not offer the same JOIN conditions as a traditional RDBMS.

    You can however use a subquery to achieve your expected output. 

    Please check the examples here 

    Do let me know if you have further questions.

    Friday, September 20, 2019 12:51 PM
    Moderator