LEFT JOIN in Azure CosmosDB SQL API

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"
  }
]

Jay Gong Jay Gong · Accepted Answer · 2019-09-20T02:28:29

As i know, left join is not supported by cosmos db so far,you could vote up this thread.

As workaround,i suggest you using 2 different sql in stored procedure, then merge the results in it.

1.SELECT base.FirstName, base.LastName FROM ContactPerson base where NOT IS_DEFINED(base.Address)

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

SP:

function sample() {
    var collection = getContext().getCollection();

    var array = [];
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT base.FirstName, base.LastName, Address.City FROM ContactPerson base JOIN Address IN base.Address',
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
                array.push(feed);
        }
    });

    var isAccepted1 = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT base.FirstName, base.LastName FROM ContactPerson base where NOT IS_DEFINED(base.Address)',
    function (err, feed1, options) {
        console.log(222)
        if (err) throw err;
        if (!feed1|| !feed1.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {

                array.push(feed1);

        }
    });
    var response = getContext().getResponse();
    response.setBody(array);

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

You could adjust the format of output as you want.

LEFT JOIN in Azure CosmosDB SQL API

2 Answers