How to build a CAML query from a string of data in JavaScript?

Question

I have a string 'listOfUsers' which is in the format user1;user2;user3;user4;user5;user6;

This string could have any number of users.

I would like to build a CAML query which will find all those users.

The query to generate will look like this:

<View>
    <Query>
        <Where>
            <Or>
                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user1</Value></Eq>
                <Or>
                    <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user2</Value></Eq>
                    <Or>    
                        <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user3</Value></Eq>
                        <Or>    
                            <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user4</Value></Eq>
                            <Or>    
                                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user5</Value></Eq>
                                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user6</Value></Eq>
                            </Or>
                        </Or>
                    </Or>
                </Or>
            </Or>
        </Where>
    </Query>
</View>

But I am unsure how to build it in JavaScript where I take a string of users and generate the CAML query.

Are you using plain JavaScript in SPFx web part? Are you comfortable with typescript example? — Ganesh Sanap - MVP, Commented Feb 27, 2023 at 13:14
Check code given in my answer below. Let me know if it works for you. — Ganesh Sanap - MVP, Commented Feb 27, 2023 at 13:22

Ganesh Sanap - MVP · Accepted Answer · 2023-02-27 13:22:14Z

I have created helper functions to dynamically generate filter conditions to use in CAML query. check this:

Helper functions:

  //Construct filter string required to fetch data from list using CAML
  public createFilterString(filterSelection: string[], fieldName: string): string[] {
    let filterStringCollection: string[] = [];
    filterSelection.map((aItem) => {
      filterStringCollection.push(
        `<Eq>
            <FieldRef Name="` + fieldName + `" />
            <Value Type="Text">` + aItem + `</Value>
          </Eq>`
      );
    });
    return filterStringCollection;
  }

//Append operator to filter string
  public getFilterStringByOperator(filterCollection: string[], operator: string): string {
    let filterString = "";
    if (filterCollection.length === 1)
      filterString += filterCollection[0];
    else if (filterCollection.length === 2)
      filterString += `<` + operator + `>` + filterCollection[0] + filterCollection[1] + `</` + operator + `>`;
    else {
      for (let i = 0; i < filterCollection.length; i++) {
        if (i == 0)
          continue;

        if (i == 1) {
          filterString += `<` + operator + `>` + filterCollection[0] + filterCollection[1] + `</` + operator + `>`;
          continue;
        }

        filterString = `<` + operator + `>` + filterString + filterCollection[i] + `</` + operator + `>`;
      }
    }
    return filterString;
  }

You can use those like:

let userEmailsStr: string = "user1;user2;user3;user4;user5;user6";
let userEmailsArr: string[] = userEmailsStr.split(";");

let arrFilterString = createFilterString(userEmailsArr, "onPremisesSamAccountName");
let filterString = getFilterStringByOperator(arrFilterString, "Or");

let camlQuery = `<View><Query><Where>` + filterString + `</Where></Query></View>`;

do you know if it's possible to order by the same sequence as i filter by? currently it doesn't display in the same order as it filters by. thanks — Pete Whelan, Commented Mar 3, 2023 at 16:58
You can try using OrderBy element in your query: learn.microsoft.com/en-us/sharepoint/dev/schema/… — Ganesh Sanap - MVP, Commented Mar 3, 2023 at 18:37
I don't think it is possible with CAML. You have to sort the result array after you get response. You can do it using JavaScript: stackoverflow.com/questions/1129216/… — Ganesh Sanap - MVP, Commented Mar 5, 2023 at 9:43

AymKdn · Accepted Answer · 2023-02-27 14:20:21Z

I created a library (SharePointPlus) that permits to deal with SharePoint API. Part of this lib, I have a function to parse a "SQL-like" string to CAML.

The function is hosted there and it's called $SP().parse.

For example, you could write $SP().parse('onPremisesSamAccountName = "user1" OR onPremisesSamAccountName = "user2"'); and it will return '<Or><Eq><FieldRef Name="onPremisesSamAccountName" /><Value Type="Text">user1</Value></Eq><Eq><FieldRef Name="onPremisesSamAccountName" /><Value Type="Text">user2</Value></Eq></Or>'

It's handy because you can write complex SQL-like sentences and get the CAML equivalent.

You probably don't want to install my whole lib, so below it's the extracted part that you need:

function _cleanString(str) {
  return str.replace(/&(?!amp;|lt;|gt;)/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");
}

function parse(q, escapeChar) {
  // schema: https://docs.microsoft.com/en-us/sharepoint/dev/schema/query-schema
  var queryString = q.replace(/(\s+)?(=|~=|<=|>=|~<>|<>|<|>| LIKE | IN )(\s+)?/g,"$2").replace(/""|''/g,"Null").replace(/==/g,"="); // remove unnecessary white space & replace '
  // the Null doesn't work with IN, so we need to move it outside
  if (/\w+ IN \[([^[]+,)?Null,?/.test(queryString)) {
    // eslint-disable-next-line
    queryString = queryString.replace(/(\w+) IN \[([^\[]+,)?Null(,[^\]]+)?\]/g,"($1 = Null OR $&)") // eslint-disable-next-line
                             .replace(/\[([^\[]+,)?Null(,[^\]]+)?\]/g,"[$1$2]")
                             .replace(/(\[),|(,),|,(\])/g,"$1$2$3");
  }

  var factory = [];
  escapeChar = (escapeChar===false ? false : true)
  var limitMax = queryString.length;
  var closeOperator="", closeTag = "", ignoreNextChar=false;
  var lastField = "";
  var parenthesis = {open:0};
  var lookupId = false;
  for (var i=0; i < queryString.length; i++) {
    var letter = queryString.charAt(i);
    switch (letter) {
      case "(": // find the deepest (
        var start = i;
        var openedApos=false;
        while (queryString.charAt(i) == "(" && i < limitMax) { i++; parenthesis.open++; }

        // find the corresponding )
        while (parenthesis.open>0 && i < limitMax) {
          i++;
          // if there is a ' opened then ignore the ) until the next '
          var charAtI = queryString.charAt(i);
          if (charAtI=="\\") ignoreNextChar=true; // when we have a backslash \ then ignore the next char
          else if (!ignoreNextChar && (charAtI=="'" || charAtI=='"')) openedApos=!openedApos;
          else if (!ignoreNextChar && charAtI=="(" && !openedApos) parenthesis.open++;
          else if (!ignoreNextChar && charAtI==")" && !openedApos) parenthesis.open--;
          else ignoreNextChar=false;
        }

        var lastIndex = factory.length-1;

        // concat with the first index
        if (lastIndex>=0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += parse(queryString.substring(start+1, i));
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          closeOperator = "";
        } else {
          factory[0] = parse(queryString.substring(start+1, i));
        }
        break;
      case "[": // for operator IN
        var start = i; // eslint-disable-line
        var openedApos=false; // eslint-disable-line
        // find the corresponding ]
        while (i < limitMax) {
          i++;
          // if there is a ' opened then ignore the ) until the next '
          var charAtI = queryString.charAt(i); // eslint-disable-line
          if (charAtI=="\\") ignoreNextChar=true; // when we have a backslash \then ignore the next char
          else if (!ignoreNextChar && (charAtI=="'" || charAtI=='"')) openedApos=!openedApos;
          else if (!ignoreNextChar && !openedApos && charAtI=="]") break;
          else ignoreNextChar=false;
        }

        var lastIndex = factory.length-1; // eslint-disable-line
        var arrIn = JSON.parse('[' + queryString.substring(start+1, i) + ']');
        // we want to detect the type for the values
        var typeIn = "Text";
        switch(typeof arrIn[0]) {
          case "number": typeIn = "Number"; break;
          default: {
            // check if it starts with ~ and then it's a number -- lookupid
            if (arrIn[0].charAt(0) === "~" && typeof (arrIn[0].slice(1)*1) === "number") {
              typeIn = "Integer";
              // change all array values
              arrIn.forEach(function(e,i) { arrIn[i]=e.slice(1) })
            }
          }
        }
        factory[lastIndex] += '<FieldRef Name="'+lastField+'" '+(typeIn==="Integer"?'LookupId="True"':'')+' /><Values><Value Type="'+typeIn+'">' + arrIn.join('</Value><Value Type="'+typeIn+'">') + '</Value></Values>' + closeTag;
        lastField = "";
        closeTag = "";
        // concat with the first index
        if (lastIndex>0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += factory[lastIndex];
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          delete(factory[lastIndex]);
          closeOperator = "";
        }
        break;
      case ">":  // look at the operand
      case "<":
        i++;
        if (queryString.charAt(i) == "=") { // >= or <=
          factory.push("<"+(letter==">"?"G":"L")+"eq>");
          closeTag = "</"+(letter==">"?"G":"L")+"eq>";
        } else if (letter == "<" && queryString.charAt(i) == ">") { // <>
          factory.push("<Neq>");
          closeTag = "</Neq>";
        } else {
          i--;
          factory.push("<"+(letter==">"?"G":"L")+"t>");
          closeTag = "</"+(letter==">"?"G":"L")+"t>";
        }
        break;
      case "~": // special operator '~=' and '~<>' for lookup
        if (queryString.charAt(i+1) == "=" || (queryString.charAt(i+1) === '<' && queryString.charAt(i+2) === '>')) {
          lookupId=true;
        }
        break;
      case "=":
        factory.push("<Eq>");
        closeTag = "</Eq>";
        break;
      case " ": // check if it's AND or OR
        if (queryString.substring(i,i+5).toUpperCase() == " AND ") {
          // add the open tag in the array
          closeOperator = "And";
          i+=4;
        }
        else if (queryString.substring(i,i+4).toUpperCase() == " OR ") {
          // add the open tag in the array
          closeOperator = "Or";
          i+=3;
        }
        else if (queryString.slice(i,i+6).toUpperCase() == " LIKE ") {
          i+=5;
          factory.push("<Contains>");
          closeTag = "</Contains>";
        }
        else if (queryString.slice(i,i+4).toUpperCase() == " IN ") {
          i+=3;
          factory.push("<In>");
          closeTag = "</In>";
        }
        else lastField += letter;
        break;
      case '"': // look now for the next "
      case "'":
        var apos = letter;
        var word = "", other="";
        while ((letter = queryString.charAt(++i)) != apos && i < limitMax) {
          if (letter === "\\" && queryString.charAt(i+1) === apos) letter = queryString.charAt(++i);
          word+=letter;
        }
        lastIndex = factory.length-1;
        factory[lastIndex] += '<FieldRef Name="'+lastField+'" '+(word=="[Me]"?'LookupId="True" ':'')+'/>';
        lastField = "";
        var type = "Text"; //(isNaN(word) ? "Text" : "Number"); // check the type
        // check automatically if it's a DateTime
        if (/\d{4}-\d\d?-\d\d?((T| )\d{2}:\d{2}:\d{2})?/.test(word)) {
          type="DateTime";
          // check if we want to evaluate the TIME also
          if (/\d{4}-\d\d?-\d\d?((T| )\d{2}:\d{2}:\d{2})/.test(word)) other=' IncludeTimeValue="TRUE"';
        }
        if (escapeChar) word = _cleanString(word);
        // special words ([Today] and [Me])
        if (word === "[Me]") {
          word = '<UserID Type="Integer" />';
          type = "Integer";
        } else if (word.slice(0,6) == "[Today") {
          type="DateTime";
          // find the offset if defined
          word = '<Today OffsetDays="'+(1*word.slice(6,-1))+'" />';
        }

        factory[lastIndex] += '<Value Type="'+type+'"'+other+'>'+word+'</Value>';
        factory[lastIndex] += closeTag;
        closeTag = "";
        // concat with the first index
        if (lastIndex>0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += factory[lastIndex];
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          delete(factory[lastIndex]);
          closeOperator = "";
        }
        break;
      case "0": case "1": case "2": case "3": case "4": case "5": case "6": case "7": case "8": case "9":
        if (closeTag != "") { // it's the value
          var value = letter;
          while (!isNaN(letter = queryString.charAt(++i)) && i < limitMax) value+=""+letter;
          lastIndex = factory.length-1;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'"'+(lookupId?' LookupId="True"':'')+' />';
          lastField = "";
          factory[lastIndex] += '<Value Type="'+(lookupId?"Integer":"Number")+'">'+value.replace(/ $/,"")+'</Value>';
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
          i-=2;
          break;
        }
      default: // eslint-disable-line
        if (closeTag == "") lastField += letter;
        else if (letter.toLowerCase() == "n" && queryString.substring(i,i+4).toLowerCase() == "null") { // if we have NULL as the value
          lastIndex = factory.length-1;
          if (closeTag == "</Neq>") { // <>
            factory[lastIndex] = "<IsNotNull>";
            closeTag = "</IsNotNull>";
          } else if (closeTag == "</Eq>") { // =
            factory[lastIndex] = "<IsNull>";
            closeTag = "</IsNull>";
          }
          i+=3;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'" />';
          lastField = "";
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
        }
        else if ((letter.toLowerCase() === "t" && queryString.substring(i,i+4).toLowerCase() === "true") || (letter.toLowerCase() === "f" && queryString.substring(i,i+5).toLowerCase() === "false")) { // when we have TRUE/FALSE as the value
          lastIndex = factory.length-1;
          i+=3;
          if (letter.toLowerCase() === "f") i++;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'" /><Value Type="Boolean">'+(letter.toLowerCase() === "t"?1:0)+'</Value>';
          lastField = "";
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
        }
    }
  }
  return factory.join("");
}

Now, if you have an array of users, you could do:

var users = [ "user1", "user2", "user3", "user4" ];
parse(users.map(function(user) { return 'onPremisesSamAccountName = "'+user+'"'}).join(" AND "));

You can also complexify if you need to have an "OR", or to compare with some other columns.

MadChipmunk03 · Accepted Answer · 2024-07-03 11:59:50Z

0

Tip: check out the <In /> operator to get rid of all the <Or /> elements

https://blog.dan-toft.dk/2023/03/caml-query-in-2023/#the-in-statement

answered Jul 3 at 11:59

MadChipmunk03

1

Add a comment |

Stack Exchange Network

How to build a CAML query from a string of data in JavaScript?

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sharepoint-online
javascript
spfx
spfx-webparts
caml-query
or ask your own question.

Linked

Hot Network Questions

How to build a CAML query from a string of data in JavaScript?

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sharepoint-onlinejavascriptspfxspfx-webpartscaml-query or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sharepoint-online
javascript
spfx
spfx-webparts
caml-query
or ask your own question.