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”