Sunday, May 4, 2014

S15E03 Query Variables – Constant Trouble

Want the book? Go get it!

This is the third episode in the series “SharePoint Search Queries Explained - The Series”. See the intro post for links to all episodes.

The basis for doing query transformations in Result Sources, Query Rules and in search web parts is to specify KQL queries, and doing so dynamically is a big bonus. This is where Query Variables come into play. Knowing KQL syntax and variable usage will enable you to craft a query for just about anything – as long as you also know the managed properties to query against which is a topic by itself.
As an example the Local SharePoint Results result source is specified as

{?{searchTerms} -ContentClass=urn:content-class:SPSPeople}

The variable parts are the ones enclosed with braces {}. {searchTerms} will for example be substituted with the query terms coming in. If you search for

author:"mikael svenson"

the final query sent to the search engine will be expanded to 
author:"mikael svenson" -ContentClass=urn:content-class:SPSPeople

As for the {?..} notation enclosing the full statement, this means that if the variables inside this statement are blank or empty then everything between {? and the matching end brace } will be omitted from the expanded query. If an empty query comes in, the final query is blank and not

-ContentClass=urn:content-class:SPSPeople

as everything is removed due to {searchTerms} being empty. This is a very powerful and important notation to know. Use it around any variable which might be empty to ensure a valid query syntax, and I will gives several samples later in this episode as well.

Out of the box query variables

The TechNet article Query variables in SharePoint Server 2013 lists all the out of the box variables available to you. And there are quite a few of them. Below you will see the variables and sample expanded values for each variable.

Note: The exact definitions for the variables can be found at TechNet.

Site and site collection properties

These are variables are context aware to the current site or site collection on the page you are currently viewing. It’s important to note that they use Site/SiteCollection and not Site/Web as is the programmatically equivalent and used in managed property names.

Query variable Examples
{Site} or {Site.URL} http://intranet.contoso.com/sites/it/phone
http://intranet.contoso.com/sites/it
{Site.ID} The GUID id for the site:
8740ca76-da13-474b-9389-0f56bf3e69de
{Site.LCID} 1033 (=English)
1044(=Norwegian)
{Site.Locale} en-US
no-NB
{Site.<property>} Any property from the property bag of an SPWeb object.

{Site.vti_defaultlanguage} return the default locale of the site, same as {Site.Locale}
{SiteCollection} or {SiteCollection.URL} http://intranet.contoso.com/
http://intranet.contoso.com/sites/it
{SiteCollection.ID} The GUID id for the site collection:
0dc39501-02df-47f9-bc9d-351024a93a74
{SiteCollection.LCID} 1033 (=English)
1044(=Norwegian)
{SiteCollection.Locale} en-US
no-NB
{SiteCollection.<property>} Any property from the property bag of the root site of the site collection

{SiteCollection.taxonomyhiddenlist} returns the GUID of the hidden taxonomy list

Page, URL token, query string and request properties

These variables are context aware to the current page’s properties or the URL you are using.

Query variable Examples
{Page} or {Page.URL} http://intranet.contoso.com/sites/it/ phone/SitePages/lumia.aspx
{Page.UsageAnalyticsId} The GUID id for the site:

8740ca76-da13-474b-9389-0f56bf3e69de
{Page.<FieldName>} Any property/column from the page.

{Page.Author} return the Created by user
”Mikael Svenson”

{Page.Editor} return the Last modified by user
”John Doe”
{URLToken.<integer>} For the URL https://intranet/testsite1/SitePages/
search.aspx


{URLToken.1} = search.aspx
{URLToken.2} = SitePages
{URLToken.3} = testsite1
{QueryString.<ParameterName>} For the URL https://intranet/testsite1/SitePages/ search.aspx?ItemNumer=567

{QueryString.ItemNumber} = 567
{Request.<PropertyName>} I have only managed to get {Request.Url) and {Request.RawUrl} to return values.

{Request.RawUrl} return the full URL including query string parameters while {Request.Url} return the URL without query string parameters.

MSDN  property reference

User Properties

Any property available on a users profile is retrievable via the {User.} variable prefix.

Query variable Examples
{User} or {User.Name} John Doe
{User.Email} john@contoso.com
{User.SID} SID of the user who issued the query (have not gotten this to work)
{User.LCID} 1033
{User.PreferredContentLanguage} -1 if not set
{User.PreferredDisplayLanguage} 1033
{User.<property>} {User.WorkPhone} will return the phone number of a user, and {User.SPS-Interests} will return the interests filled out by the user. Also see the {|…} multi-value expanstion.

Term and term set properties

The term variables are related to navigation in SharePoint which is using the term store. In addition they work for item properties which are taxonomy columns.

Query variable Examples
{Term} or {Term.ID} or {Term.IDNoChildren} GUID of current site navigation node with a prefix of #0 — for example, #083e99dcb-7907-4dc9-abc8-b5614a284f1c.

This value can be used to query content of the managed property owstaxIdMetadataAllTagsInfo or owstaxIdProductCatalogItemCategory in a Product Catalog Site Collection, or any other taxonomy based managed property owstaxIdSiteColumnName
{Term.IDWithChildren} GUID of current site navigation node with a prefix of # — for example, #83e99dcb-7907-4dc9-abc8-b5614a284f1c. This will return all items tagged with the current site navigation term, or children of the current site navigation term.

This value can be used to query content of the managed property owstaxIdProductCatalogItemCategory in a Product Catalog Site Collection, or any other taxonomy based managed property owstaxIdSiteColumnName

This value cannot be used to query the content of the managed property owstaxidmetadataalltagsinfo.
{Term.Name} The label of the term, for example
Blue
{Term.<property>} Any property from the property bag of the term, including custom properties.
{TermSet} or {TermSet.ID} GUID of the term set used for current site navigation.
{TermSet.Name} Label of the term set used for current site navigation.

List and list item properties

List variables are useful when you add search based content on a list view or on an item page. An item page can also be a wiki page or a publishing page.

Query variable Example
{List} https://contoso.com/sites/pub/Documents
{List.<property>} Any property of the current list (but I have only gotten ID and Title to return a value)

{List.ID} return the GUID of the list
{List.Title} return the name of the list
{ListItem} URL of the current list item.
https://contoso.com/sites/pub/Pages/ArticleTest.aspx
{ListItem.<property>}





{ListItem.<tax>
.<property>}
Property is internal column name.

{ListItem.ID} - 3
{ListItem.Title} - My Title
{ListItem.author} - Mikael Svenson

If you have a column named Comments
{ListItem.Comments} - This is a comment

If you have a taxonomy column named TaxCol
{ListItem.TaxCol.IDWithChildren}


Other properties

Query variable Examples
{Today+/- <integer value for number of days>} {Today} - 2014-04-26
{Today+10} - 2014-05-06
{Today-365} - 2013-04-26
{SearchBoxQuery} The query value entered into a search box on a page.
{CurrentDisplayLanguage} en-US
{CurrentDisplayLCID} 1033

Custom query variables

Using custom development you can insert custom variables to be used in your queries as well. You can look at the MSDN reference for User Segmentation to get an overview of this, or check out my code from SPC12 where I add information about who you follow into variables {FollowedUsers} and {FollowedSites}, which are multi-value variables.

If you want to add custom variables in SharePoint Online, you will have to resort to some JavaScript trickery which I will touch on in the episode about context triggering of query rules.

Variables with spaces or special characters in the values

Values which have spaces or special characters in them will be expanded with quotation marks around them.

author:{User.Name}

expands to

author:"Mikael Svenson"


If you don’t want to enclose the value in quotation marks you can escape the variable with {\

author:{\User.Name}

expands to

author:Mikael Svenson


which in the above case would match an Author named Mikael, and any content including Svenson.

Query variables with multiple values

Query variables can contain multiple values, and by using the syntax {|ManagedProperty:{QueryVariable}} or {|{QueryVariable}}

The expansion will be made using the OR operator, and there is no option for AND expansion. If a user has set the Ask me about field on the user profile to: SharePoint, Search and Apps, then the transformation

{|{User.SPS-Responsibility.Name}}

expands into

((SharePoint) OR (Search) OR (Apps))

Samples

Sample 1

The query below will only show AllItems.aspx results for the current site’s default Document library. Note the quotes around the full statement and escaping of the variable.

path:"{\Site.URL}/Forms/Pages/Forms/AllItems.aspx"

path:"https://sharepoint.com/sites/pub/Forms/Pages/Forms/AllItems.aspx"

Sample 2

On a page, show all other pages (omit self) in the same library tagged with term or child term on a taxonomy column named Category.

owstaxIdCategory:{ListItem.Category.IDWithChildren} ListID:{List.ID}  -ListItemID:{ListItem.ID}

or

owstaxIdCategory:{Page.Category.IDWithChildren} ListID:{List.ID} -ListItemID:{ListItem.ID}

owstaxIdTestCategory:#649bf5dc-b26d-4e63-8c97-43967f08f516 ListID:0c8e9283-ded5-4d4f-bd51-152283a67944 -listitemid:3


or


owstaxIdCategory:{ListItem.Category.IDWithChildren} path:"{\Site.URL}/Pages/" -path:{Page.Url}

owstaxIdCategory:#649bf5dc-b26d-4e63-8c97-43967f08f516 path:"https://sharepoint.com/sites/pub/Pages/" -path:"https://sharepoint.com/sites/pub/Pages/ArticleTest.aspx"

Sample 3

On a page, show all other pages in the farm tagged with term or child term on a taxonomy column named Category, and boost equal items with a custom factor from the custom property on the term used. If there is no priority property on the term used, the XRANK part is omitted.

owstaxIdTestCategory:{ListItem.TestCategory.IDWithChildren} {?XRANK(cb={ListItem.TestCategory.Priority}) owstaxIdTestCategory:{ListItem.TestCategory}}

image

owstaxIdTestCategory:#649bf5dc-b26d-4e63-8c97-43967f08f516 XRANK(cb=1) owstaxIdTestCategory:#0649bf5dc-b26d-4e63-8c97-43967f08f516

Sample 4

Show items tagged with the same tag as a users ask me about fields. I’m using the general catch all taxonomy column here, but you can use any custom taxonomy column. I have also added {?…} which would remove the query if the user has no values in that field.

{?{|owstaxidmetadataalltagsinfo:{User.SPS-Responsibility}}}

((owstaxidmetadataalltagsinfo:#00cf9414f-84e4-40ff-8cd7-d3e0a5170887) OR (owstaxidmetadataalltagsinfo:#00b88c4ac-8318-4b3f-b6db-2b2e208fc21c) OR (owstaxidmetadataalltagsinfo:#03da2996f-902d-46b9-96a9-610fd848adc7))

Sample 5

Find items which title contains values from a users ask me about.

{|title:{User.SPS-Responsibility.Name}}

((title:SharePoint) OR (title:Search) OR (title:Apps))

Sample 6

Pass in a value from the URL query parameter foo. http://contoso.com/Pages/Article.aspx?foo=bar

title:{QueryString.foo}

title:bar

Pass in a value from the URL query parameter foo. http://contoso.com/Pages/Article.aspx?foo=bar bar

title:”bar bar”

28 comments:

  1. This is a great reference and examples Mikael. Understanding the expansion of managed metadata columns via {|:...} is a great help. Thank you.

    ReplyDelete
  2. Hi Mikael, this just a bit of an FYI note regarding the expansion technique you mentioned above for Multi Select Managed Metadata columns (e.g. {|:...} and anonymous access users. I'm not sure if you've come across it or not.

    I've been working on a public facing site recently which allows you to create a page and tag it with multiple related pages. I was then using the technique you noted above and the Content Search WebPart to pull back these related pages via a query something like this: {?{|Title:{Page.RelatedTopicPages.Name}}}... This worked fine when I was logged in but when I tested it as an anonymous user it failed.

    I then ran across this blog post (http://exploringsp2013.wordpress.com/2014/01/11/multi-select-managed-metadata-bug)... which spoke about a bug with multi select managed metadata columns and found that this was the problem.

    Thanks again for this great reference.

    Regards
    Pete

    ReplyDelete
    Replies
    1. Hi,
      Thanks for noting this - and haven't run across it yet (as I'm not doing much WCM stuff; )

      Let's hope this is indeed a bug and not a feature and that it will later be fixed. - which means - log it as a service request if you haven't :)

      Delete
  3. Excellent article.Just used {|managed property:...} syntax in a query.Worked like a charm.

    ReplyDelete
  4. I have a curious problem with multi-value metadata.
    I have a list of learning resources which may be tagged for multiple subjects (managed metadata) such as "Marketing", "Digital Marketing" etc. Notice that one value may be a substring of another - this happens quite a lot.

    A search query for "subject:Marketing" (using the contains operator) returns resources for both Marketing and Digital Marketing (as expected)
    A search query for "subject=Marketing" (using the equals operator) returns resources for Marketing and not Digital Marketing, but does not return the items if the managed metadata field contains any other values. (not expected)

    I would like to find a syntax that allows me to show items where one of the values of the managed metadata field is exactly "Marketing"


    Any ideas?
    (By the way I have LOVED this series on Sharepoint search - seems to be unique on the web in its depth and quality)

    ReplyDelete
    Replies
    1. Thank you..and you have to use the term ID's and not the names to avoid sub-string issues.

      Delete
  5. Hi Mikael,

    This is a fantastic article! I am always coming back to this page when I am working with Content By Search. I did want to pick your brain with a question though, would you happen to know the syntax to return all blank / null values for a managed property?

    I am trying to do something along the lines of:
    ContentTypeId:0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF39003F11102A7C0343B0836EDF8DA14F33ED00B679312D96584105BCFD3A33F39BD3AF* AND Theme:Innovation AND EXEExecutive='' (not sure what the syntax is for is blank). I have a suspicion that I need to wrap the Executive managed property with {?} but I just can't seem to get the syntax correct.

    Have any ideas?

    Thanks Again,
    Dan

    ReplyDelete
    Replies
    1. You can only find all items not having a value, not all empty ones.. for most cases. If you know all possible values, do a search to exclude them. For dates find items outside the interval you know have dates to find empty ones.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Great article Mikael... Lots of good detail...

    Wondering if you could peak at this one... Hoping for a hint on this...

    To put it simply, I am inserting the CSWP on a standard list display form. The list is the Client list and I'm looking to search for all case notes or documents related to this client using the CSWP.
    I have search working 100% from the standard search forms, and the Search Box executes it fine, but I cannot for the life of me get the "{Page.CF_ClientID}" variable to work...

    Here is the expanded detail.

    I have a SharePoint 2013 list (upgraded from 2010 originally) called Clients and I want to display search results specific to the Client on the Display Form for that list. We are current using the Content Query Web Part to do this and I'm hoping to leverage SharePoint Search to improve the performance.

    Anyway, I have added the Content Search Web Page to the page and configured with a value for the Client ID hard coded in the query as follows:

    ClientLookup:9999Z AND (contentclass:STS_ListItem_10002)

    The value "9999Z" is an actual client ID and it works properly, it is just querying for the wrong client.

    When I attempt to replace the hard coded value "9999Z" with "Value of a field on the page" keyword I get an error in the ULS logs and no results returned.

    Here is the updated query.

    ClientLookup:{Page.CF_ClientID} AND (contentclass:STS_ListItem_10002)

    And, here is the error.

    QueryTemplateHelper: Cannot replace parameters in template. The value for parameter Page.CF_ClientID is missing. Parameter : Page.CF_ClientID, Template: ClientLookup:{Page.CF_ClientID} AND (contentclass:STS_ListItem_10002) .

    I have also tried using this as the query and get the same result.

    ClientLookup:{ListItem.CF_ClientID} AND (contentclass:STS_ListItem_10002)

    Thoughts?

    ReplyDelete
    Replies
    1. Is CF_ClientID the internal name of the field? And do you have the variable directly in the CSWP or via a result source?

      Delete
  8. CF_ClientID is the static, internal name... Display name is "Client ID".

    ReplyDelete
  9. Sorry... Fired my answer too fast... The parameter is in the Result Source...

    ReplyDelete
  10. Mikael... By the looks of it, it wasn't working because the parameter was in the result source... That was quite a few hours of work down the drain! I wish Microsoft had clarified that.

    ReplyDelete
  11. Great series.

    A couple of small points, though :-)

    First of all, I've found that AND-expansion of multi-select query-variables. I do something like this ALL({User.Interests}).

    Additionally, I've found the following is a great example of semi-advanced schema management (that you can perhaps use as an example): I had a user that wanted to show classic alphabet navigation, where a click on the letter "A" would give all results that had a title startin with "A" - the issue was that a simple search on "Title:a*" also would give results where the second word in a title started with A (e.g. "Swedish Attendance") - to get past this, I created a new managed property and set its length to 1 byte using powershell, and mapped it to ows_title. Now doing a property-search would yield the requested result (titleFirstChar:A*)

    ReplyDelete
    Replies
    1. Nice usage of ALL() and multi-variables indeed! As for your directory solution that works fine for on-premises and is pretty cool!, but a bit hard to solve for SPO.

      Delete
  12. Mikael, this is a nice explanation of a these query variables. I feel like I'm having trouble with this most basic concept, and maybe you can point me in the right direction.

    We have a requirement to show a "Related Links" SRWP on each page of our site. So we have a managed property called owstaxIdFeatures where the author can enter multiple values. The "Related Links" SRWP should then show any pages (omit self) whose owstaxIdFeatures contain any of the same values. Seems pretty straightforward, right?
    Using the information in this post, here's the query:
    (|owstaxIdFeatures:{Page.owstaxIdFeatures})
    I've tried several variations of this query, but I never get the expected results. I may get some of the pages that contain one or more of the same features as the page, but not all of them. Am I just not understanding the usage of these variables? Is there a better way to accomplish this? Any guidance you can provide would be truly appreciated!

    ReplyDelete
    Replies
    1. Make sure duplicate trimming is turned off, and use the TEST tab to see how the variables are expanded on that particular page, seeing the full query being ran.

      You could then take that output and use it in the SP 2013 Query Tool, and see if you get all items or not. And also check what the value is in that field for the items you are missing.

      Hope this helps you to debug this issue, as the query itself looks pretty sane to me. You can also try {Page.owstaxIdFeatures.Id} or {Page.owstaxIdFeatures.Name} (I think it is(?), or .Label), and see if that output works better for you.

      Delete
    2. Thanks, Mikael. Looking at the TEST tab, this is how the Query Text looks:
      {owstaxIdFeature:{Page.owstaxIdFeature}} -ContentClass=urn:content-class:SPSPeople
      I know that last bit has to do with the result source being "Local SharePoint Results," but when you say the variables should be "expanded" does that mean I should actually see what values are being rendered for the query for this page? Is it meaningful that I don't see the variables actually being rendered?

      Sorry, I'm a bit of a newbie...

      Delete
    3. Ah....do (|owstaxIdFeatures:{Page.IdFeatures})

      The variable takes the items internal column name, not the managed property name :)

      Delete
    4. You have just made my week! So using the internal column of the page was the missing key. Here's the syntax that worked (and I could actually SEE the IDs of the column expanded on the TEST tab YAY! ;-):
      {|owstaxIdFeatures:{Page.Features}}
      Thank you so very much for your prompt, kind, and helpful replies!

      Delete
    5. Sorry, one more question. What's the best way to omit the current page from these results? I'm trying:
      ({|owstaxIdFeatures:{Page.Features}} -{Page.URL})
      with no effect (I've tried it with or without the parentheses.
      Sorry to bother...

      Delete
    6. Please ignore that...this works
      {|owstaxIdFeatures:{Page.Features}} -Path:{Page.URL}

      Delete
    7. See sample 2 in this post :) It uses id and is more efficient compared to a string :) If it matters.

      Delete
  13. Mikael,

    Great post! For some reason, I cannot get the pipe to work in a CSWP. If I do something like `Title:foo OR Title:bar`, I am able to retrieve files that only have foo or bar in the title.

    When I try to automate that via the query string: `https://....aspx?subject=foo&subject=bar`, I am not having luck with the results. I've tried many things, which would be too many to list: 1:`(|Title:{QueryString.subject})` 2:`{|Title:{QueryString.subject}}` 3:`{? ({|Title:{QueryString.subject}}) }`


    This content search web part is built on a result source, that uses Local SharePoint protocol with query transforms as `{searchTerms} (site:http://.../site1 OR site:http://.../site2)`

    Oddly enough, single values work: `{? ({Title:{...}}) }`, just not when adding a pipe to expand it. There must be something wrong with my syntax or how I'm passing the multiple values through the query string.

    Thanks for any help you can provide. I plan to help others on Stack Exchange: http://sharepoint.stackexchange.com/questions/128053/content-search-webpart-query-string-filter-with-multiple-values

    ReplyDelete
    Replies
    1. I haven't tested with multiple query args with the same name, but my guess is that it's not supported. You could fix it client side or just use multiple arguments with unique names instead.

      Delete
    2. Still Kudos to your being the first (and so far only) source I have found that mentions the expansion-OR operator.

      Your post here: https://social.technet.microsoft.com/Forums/office/en-US/e8ffb8bb-446a-462a-8a97-a36231c201d5/how-to-read-multiple-querystring-parameters-in-search-result-source was also helpful

      It's a shame that query arguments do not work -- that seems to be an unfortunate limitation, since I hope to build a search filter based on list values. The values and quantity may change, so fixed argument names was what I was hoping to avoid (as well as reducing number of characters in the GET request)

      Delete