Transcription

OData Query

Trademark Information(TBD) and (TBD) are trademarks of SuccessFactors Inc.All other trademarks and copyrights are the property of their respective owners.No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for anypurpose, without the express written permission of SuccessFactors Inc. Under the law, reproducing includes translating intoanother language or format.As between the parties, SuccessFactors Inc. retains title to, and ownership of, all proprietary rights with respect to the softwarecontained within its products. The software is protected by United States copyright laws and international treaty provision.Therefore, you must treat the software like any other copyrighted material (e.g. a book or sound recording).Every effort has been made to ensure that the information in this manual is accurate. SuccessFactors Inc. is not responsible forprinting or clerical errors. Information in this document is subject to change without notice.Release InformationProduct Version 1308Release Date 9/2013 2013 SuccessFactors Inc. All rights reserved.Contact InformationSuccessFactors Global Headquarters1 Tower Place, Suite 1100South San Francisco, CA 94080USAToll Free: 800-809-9920Phone: 650-645-2000Fax: 650-645-2099http://www.successfactors.com/

Contents1OData Query . 11.1Query Operation . 11.2Query Links. 31.3Query String Options. 41.3.1System Query Option . 41.3.2Orderby System Query Option ( orderby) . 41.3.3Custom Query Option . 131.4Advanced Query. 131.4.1Introduction to general query. 131.4.2Comprehensive Examples . 151.5Pagination . 191.5.1How the pagination works in OData . 19

SFSF 1OData API Functional Guide1OData QueryOData is a REST based protocol for accessing a data store. The entities in the API are described in themetadata document. Accessing the entities is as simple as knowing the entity name, and thenreferencing the entity through an HTTP Get. For example, all SuccessFactors systems have a “User”entity which represents a user account. To query for all the users in a system is as simple as accessingthe following URL:https:// hostname /odata/v2/UserOf course, there are many more things to consider, including filtering, paging, sorting, joining to otherentities (known as “expanding” in OData), etc. Examples of these details are described below.Note: The OData protocol does not dictate that any particular operation must be supported. It allowsthe service provider (that means SuccessFactors for this API) to decide which operations are supported.In our system, the supported operations vary by entity. For example, our system does not allow useraccounts to be deleted. We allow them to be set as inactive only. Therefore the “User” entity does notsupport the delete operation.This functional guide does not cover the details of each entity behavior. SuccessFactors will provide anumber of separate entity guides, organized by solution, to describe the entity supported operations,business meaning and business logic, and configurations details like security and custom fieldconfiguration.1.1 Query OperationThis chapter explains how to compose an OData URI, and explains each component of the URL.Afterwards we present some query examples.In OData, a URI has the following structure:http:// hostname ][/NavPropCollection][/Complex][/Property]Below is a description of each of the parts of the above URI: EntitySet: The name of a Collection or a custom Service Operation (which returns a Collection of Entries)exposed by the service.KeyPredicate: A predicate that identifies the value(s) of the key Properties of an Entry. If the Entry has asingle key Property the predicate may include only the value of the key Property. If the key is made up oftwo or more Properties, then its value must be stated using name/value pairs. More precisely, the syntaxfor a KeyPredicate is shown by the following figure.

SFSF 2OData API Functional Guide NavPropSingle: The name of a Navigation Property defined by the Entry associated with the prior pathsegment. The Navigation Property must identify a single entity (that is, have a "to 1" relationship).NavPropCollection: Same as NavPropSingle except it must identify a Collection of Entries (that is, have a"to many" relationship).ComplexType: The name of a declared or dynamic Property of the Entry or Complex Type associatedwith the prior path segment.Property: The name of a declared or dynamic Property of the Entry or Complex Type associated with theprior path segment.The example URIs here follow the addressing rules stated above and are based on the reference service andits metadata availablehttp:// hostname /odata/v2/User Identifies all User Collection.Is described by the Entity Set named "User" in the service metadata document.http:// hostname /odata/v2/User(‘1’) Identifies a single User Entry with key value 1.Is described by the Entity Type named "User" in the service metadata document.http:// hostname /odata/v2/User(‘1’)/username Identifies the Name property of the User Entry with key value 1.Is described by the Property named "Name" on the "User" Entity Type in the service metadata document.http:// hostname /odata/v2/User(‘1’)/proxy Identifies the collection of proxy associated with User Entry with key value 1.Is described by the Navigation Property named "proxy" on the "User" Entity Type in the service metadatadocument.http:// hostname /odata/v2/User(‘1’)/proxy/ count Identifies the number of proxy Entries associated with User 1.Is described by the Navigation Property named "proxy" on the "User" Entity Type in the service metadatadocument.http:// hostname /odata/v2/User(‘1’)/proxy(‘1’)/hr/username Identifies the username of the hr for proxy 1 which is associated with User 1.Is described by the Property named "username" on the "hr" Entity Type in the service metadatadocument.

SFSF 3OData API Functional Guidehttp:// hostname / value Same as the URI above, but identifies the "raw value" of the username property.1.2 Query LinksMuch like the use of links on Web pages, the data model used by OData services supports relationships as afirst class construct. For example, an OData service could expose a Collection of proxy Entries each of whichare related to a User Entry.Associations between Entries are addressable in OData just like Entries themselves are (as described above).The basic rules for addressing relationships are shown in the following figure.http:// hostname /odata/v2/EntitySet[(keyPredicate)]/ links/navigationProperty NavigationProperty: The name of a Navigation Property that is declared on the Entry associated with thepath segment prior to the " links" segment.ExamplesThe example URIs here follow the addressing rules stated above and are based on the reference service andits metadata available at http:// hostname /odata/v2/ metadata.http:// hostname /odata/v2/User(‘1’)/ links/proxy Identifies the set of proxy related to User 1.Is described by the Navigation Property named "proxy" on the "User" Entity Type in the associatedservice metadata document.http:// hostname /odata/v2/User(‘1’)/ links/proxy Identifies the proxy related to User 1.Is described by the Navigation Property named "proxy" on the "User" Entity Type in the associatedservice metadata document.

SFSF 4OData API Functional Guide1.3 Query String Options1.3.1 System Query OptionSystem Query Options are query string parameters a client may specify to control the amount and order ofthe data that an OData service returns for the resource identified by the URI. The names of all System QueryOptions are prefixed with a " " character.An OData service may support some or all of the System Query Options defined. If a data service does notsupport a System Query Option, it must reject any requests which contain the unsupported option as definedby the request processing rules.1.3.2 Orderby System Query Option ( orderby) orderby is only supported when the resource path identifies a Collection of Entities; it uses to manage theorder of collection. By default, the order is ascending.The examples below represent the most commonly supported subset of that expression syntax.Exampleshttp:// hostname /odata/v2/User? orderby username All User Entries returned in ascending order when sorted by the username Property.http:// hostname /odata/v2/User? orderby username asc Same as the example above.http:// hostname /odata/v2/User? orderby username,hr/username desc Same as the URI above except the set of User is subsequently sorted (in descending order) by theusername property of the related hr Entry.1.3.2.1Top System Query Option ( top) top keyword is used to pick the topN from the identified collection.If the data service URI contains a top query option, but does not contain a orderby option, then the Entriesin the set needs to first be fully ordered by the data service. While no ordering semantics are mandated, toensure repeatable results, a data service must always use the same semantics to obtain a full ordering acrossrequests.Exampleshttp:// hostname /odata/v2/User? top 5

SFSF 5OData API Functional Guide The first 5 User Entries returned where the Collection of User are sorted using a scheme determined bythe OData service.http:// hostname /odata/v2/User? top 5& orderby username desc The first 5 User Entries returned in descending order when sorted by the username property.1.3.2.2Skip System Query Option ( skip)A data service URI with a skip System Query Option identifies a subset of the Entries in the Collection ofEntries identified by the Resource Path section of the URI. That subset is defined by seeking N Entries into theCollection and selecting only the remaining Entries (starting with Entry N 1). N is a positive integer asspecified by this query option. If a value less than 0 is specified, the URI should be considered malformed.If the data service URI contains a skip query option, but does not contain a orderby option, then the Entriesin the Collection must first be fully ordered by the data service. While no ordering semantics are mandated,to ensure repeatable results a data service must always use the same semantics to obtain a full orderingacross requests.Exampleshttp:// hostname /odata/v2/User(1)/proxy? skip 2 The set of proxy Entries (associated with the User Entry identified by key value 1) starting with the thirdUser.http:// hostname /odata/v2/User? skip 2& top 2& orderby username The third and fourth User Entry from the collection of all User entities when the collection is sorted byusername (ascending).1.3.2.3Filter System Query Option ( filter)A URI with a filter System Query Option identifies a subset of the Entries from the Collection of Entries. Thesubset is determined by selecting only the Entries that satisfy the predicate expression specified by the queryoption.The expression language that is used in filter operators supports references to properties and literals. Theliteral values can be strings enclosed in single quotes, numbers and boolean values (true or false) or any ofthe additional literal representations.The operators supported in the expression language are shown in the following table.

SFSF 6OData API Functional GuideOperatorDescriptionExampleLogical Operators/User? filter hr/username eq 'cgrant'EqEqualto find User whose hr/username is equal to ‘cgrant’, hr is the navigationproperty/User? filter username eq ‘cgant’to find User with its property equal to ‘cgrant’NeNot equalGtGreater than/ User? filter hr/username ne 'London'/ PicklistLabel? filter id gt 20000to find PicklistLabel whose id is greater than 20000GeGreater thanor equal/ PicklistLabel? filter id ge 10LtLess than/ PicklistLabel? filter id lt 20LeLess than orequal/ PicklistLabel? filter id le 100AndLogical and/ PicklistLabel? filter id le 1005 and id gt 1000to find PicklistLabel whose id is within range [1000,1005]OrLogical or/ PicklistLabel? filter id le 3.5 or id gt 200

SFSF 7OData API Functional GuideNotLogicalnegation/User? filter not endswith(username,'grant')Arithmetic OperatorsAddAddition/PicklistLabel? filter id add 5 gt 10SubSubtraction/ PicklistLabel? filter id sub 5 gt 10MulMultiplication / PicklistLabel? filter id mul 2 gt 2000DivDivision/ PicklistLabel? filter id div 2 gt 4ModModulo/ PicklistLabel? filter id mod 2 eq 0Grouping Operators()PrecedencegroupingCustomized Operators/ PicklistLabel? filter (id sub 5) gt 10

SFSF 8OData API Functional GuideinIn clause/User? filter userId in'ctse1','mhuang1','flynch1'& select username,userIdIdentifies Users with whose key is equal to one of specified in in clause/User? filter userId like’remy’Identifies Users with whose userId equal to remy, equivalent to filter userId eq ‘remy’/User? filter userId like’remy%’Identifies Users with whose userId starts with remy, equivalent to filter startswith(userId, ‘remy’)/User? filter userId like’%remy’Identifies Users with whose userId endswith with remy, equivalent to filter endswith(userId, ‘remy’)likeLike clause/User? filter userId like’%remy%’Identifies Users with whose userId contains string remy./User? filter tolower(userId) like’%remy%’Identifies Users with whose userId contains remy in case insencisive./User? filter toupper(userId) like’%remy%’Some as previous/User? filter toupper(userId) like’%remy%’ or tolower(username)like ‘%remy%’Identifies Users with whose userId like remy union Users with whoseusername like remy/User? filter toupper(userId) like’%remy%’ and tolower(username)

SFSF 9OData API Functional Guidelike ‘%remy%’Identifies Users with whose userId like remy and username like remyIn addition to operators, a set of functions are also defined for use with the filter query string operator. Thefollowing table lists the available functions. Note: ISNULL or COALESCE operators are not defined. Instead,there is a null literal which can be used in comparisons.OData root http:// hostname /odata/v2FunctionExampleString Functionsbool endswith(string p0,string p1)OData root/ User? filter endswith(username, 'Futterkiste')bool startswith(string p0,string p1)OData root/ User? filter startswith(username, 'Alfr')string tolower(string p0)OData root/ User? filter tolower(username) eq 'alfreds futterkiste'string toupper(string p0)OData root/ User? filter toupper(username) eq 'ALFREDSFUTTERKISTE'string trim(string p0)1.3.2.4OData root/ User? filter trim(username) eq 'Alfreds Futterkiste'Expand System Query Option ( expand)A URI with a expand System Query Option indicates that Entries associated with the Entry or Collection ofEntries identified by the Resource Path section of the URI must be represented inline (i.e. eagerly loaded). Forexample, if you want to identify a User and its proxy, you could use two URIs (and execute two requests), onefor /User(1) and one for /User(1)/proxy. The ' expand' option allows you to identify related Entries with asingle URI such that a graph of Entries could be retrieved with a single HTTP request.The syntax of a expand query option is a comma-separated list of Navigation Properties. Additionally eachNavigation Property can be followed by a forward slash and another Navigation Property to enableidentifying a multi-level relationship.Note: The filter section of the normative OData specification provides an ABNF grammar for the expressionlanguage supported by this query option.Exampleshttp:// hostname /odata/v2/User? expand proxy

SFSF 10OData API Functional Guide Identifies the Collection of User as well as each of the proxy associated with each User.Is described by the Entity Set named "User" and the "proxy" Navigation Property on the "User" EntityType in the service metadata document.http:// hostname /odata/v2/User? expand hr/matrixManager Identifies the Collection of User as well as each of the hr associated with each User. In addition, the URIalso indentifies the matrixManager associated with each hr.Is described by the Entity Set named "User", the "hr" Navigation Property on the "User" Entity Type, andthe "matrixManager" Navigation Property on the "hr" Entity Type in the service metadata document.http:// hostname /odata/v2/User? expand hr,proxy Identifies the set of User as well as the hr and proxy associated with each User.Is described by the Entity Set named "User" as well as the "hr" and "proxy" Navigation Property on the"User" Entity Type in the service metadata document.1.3.2.5Format System Query Option ( format)A URI with a format System Query Option specifies that a response to the request MUST use the media typespecified by the query option. If the format query option is present in a request URI it takes precedenceover the value(s) specified in the Accept request header. Valid values for the format query string option arelisted in the following table. format ValueatomjsonResponse Media Typeapplication/atom xmlapplication/jsonExampleshttp:// hostname /odata/v2/User? format atom Identifies all User Entries represented using the AtomPub format ?xml version '1.0' encoding 'utf-8'? feed xmlns "http://www.w3.org/2005/Atom"xmlns:m ices/metadata"xmlns:d ices"xml:base "http:// hostname /odata/v2/" title type "text" Picklist /title

SFSF 11OData API Functional Guide id http:// hostname /odata/v2/Picklist /id updated 2013-07-29T07:10:44Z /updated link rel "self" title "Picklist" href "Picklist" / entry id http:// hostname /odata/v2/Picklist('CandidateStatus') /id title type "text" / updated 2013-07-29T07:10:44Z /updated author name / /author link rel "edit" title "Picklist" href "Picklist('CandidateStatus')" / linkrel ices/related/picklistOptions"type "application/atom xml;type entry" title "picklistOptions"href "Picklist('CandidateStatus')/picklistOptions" / category term "SFOData.Picklist"scheme ices/scheme" / content type "application/xml" m:properties d:picklistId CandidateStatus /d:picklistId /m:properties /content /entry /feed http:// hostname /odata/v2/User? format json Identifies all User Entries represented using the JSON format{"d": {"results": [{" metadata": {"uri": "http:// hostname /odata/v2/Picklist('CandidateStatus')","type": "SFOData.Picklist"},"picklistId": "CandidateStatus","picklistOptions": {" deferred": {"uri": "Picklist('CandidateStatus')/picklistOptions"}}}]

SFSF 12OData API Functional Guide}}1.3.2.6Select System Query Option ( select)A data service URI with a select System Query Option identifies the same set of Entries as a URI without a select query option; however, the value of select specifies that a response from an OData service shouldreturn a subset of the Properties which would have been returned had the URI not included a select queryoption.Version Note: This query option is only supported in OData version 2.0 and above.The value of a select System Query Option is a comma-separated list of selection clauses. Each selectionclause may be a Property name, Navigation Property name, or the "*" character. The following set ofexamples uses the data sample data model available at http:// hostname /odata/v2/ metadata to describethe semantics for a base set of URIs using the select system query option. From these base cases, thesemantics of longer URIs are defined by composing the rules below.Exampleshttp:// hostname /odata/v2/User? select username,userId In a response from an OData service, only the username and userId Property values are returned foreach User Entry.If the select query option had listed a Property that identified a Complex Type, then all Propertiesdefined on the Complex Type must be returned.http:// hostname /odata/v2/User? select username,proxy In a response from an OData service only the username Property value and a link to the related proxyEntry should be returned for each User.http:// hostname /odata/v2/User? select username,proxy& expand proxy/hr In a response from an OData service, only the username of the User Entries should be returned, but allthe properties of the Entries identified by the proxy and hr Navigation Properties should be returned.http:// hostname /odata/v2/User? select username,proxy/username& expand proxy In a response from an OData service, the username property is included and proxy Entries withusername property is included; however, rather than including the fully expanded proxy Entries, eachproxy will contain a user property.

SFSF 13OData API Functional Guide1.3.3 Custom Query OptionCustom Query Options provide an extension point for OData service-specific information to be placed in thequery string portion of a URI. A Custom Query String option is defined as any name/value pair query stringparameter where the name of the parameter does not begin with the " " character. Any URI exposed by anOData service may include one or more Custom Query Options.Exampleshttp:// hostname /odata/v2/User?purge true Identifies all User entities. Includes a Custom Query Option "purge" whose meaning is service specific.1.4 Advanced Query1.4.1 Introduction to general queryURI1 scheme serviceRoot "/" entitySet Identify entities under specified entitySet. If User entitySet was given here, then return all Usersin the response.URI2 scheme serviceRoot "/" entitySet "(" keyPredicate ")" Identify specific entity with given key under entitySet. If URI2 likes serviceRoot/User(‘1’), itidentifies entity in the User entity set with the Entity Key 1URI3 scheme serviceRoot "/" entitySet "(" keyPredicate ")/"entityComplexProperty Example : URI: ies: The value of the proxy property of the User entity identified by key value 1 in theUser Entity Set.URI4 scheme serviceRoot "/" entitySet "(" keyPredicate ")/" entityComplexProperty"/" entityProperty Example: meIdentifies: The value of the username property of the proxy ComplexType property of the Userentity identified by key value 1 in the User Entity Set.URI4-2 scheme serviceRoot "/" entitySet "(" keyPredicate ")/" entityComplexProperty"/" entityProperty/ value Same as URI4, but it only returns raw string. And last property prior to value should be in

SFSF 14OData API Functional Guide primitive type.Example : me/ valueIdentifies: The raw value of the username property of the proxy ComplexType property of theUser entity identified by key value 1 in the User Entity Set. Return value should looks like“rocky”, should have no surrounding envelop.URI5 scheme serviceRoot "/" entitySet "(" keyPredicate ")/" entityProperty Example: URI: tifies: The name of the User entity in the User EntitySet identified by key 'ALFKI'.URI5 scheme serviceRoot "/" entitySet "(" keyPredicate ")/" entityProperty/ value Example: URI: http://host/service.svc/User('ALFKI')/username/ valueIdentifies: Same as proceeding, but identifies the value of the property free of any metadata orsurrounding markup.URI6 scheme serviceRoot "/" entitySet "(" keyPredicate ")/" entityNavProperty Example: URI: serviceRoot/PicklistLabel(optionId 498L,locale 'en US')/picklistOptionIdentifies: The set of picklistOption Entity Type instances (or instances of a sub type ofpicklistOption) associated with the PicklistLabel identified by the key(optionId 498L,locale 'en US')through the picklistOption Navigation Property. If the key issingular key, just specify like User(‘userId’);URI7 scheme serviceRoot "/" entitySet "(" keyPredicate ")/ links/" entityNavProperty d 498L,locale 'en US')/ links/picklistOptionIdentifies: The collection of all Links between the entity in the PicklistLabel Entity Set identifiedby key (optionId 498L,locale 'en US') and the picklistOption entities associated with thatPicklistLabel via the picklistOption navigation property.URI: http://host/odata/v2/User(‘1’)/ links/proxyIdentifies: The Link between the User entity with key value 1 in the User Entity Set and proxyentity associated with that User via the proxy navigation property.URI8 scheme serviceRoot "/ metadata" Comment: URI: http://host/odata/v2/ metadataIdentifies: The EDMX (metadata) document for the data serviceHere is a table to summarize the general URI works with which system query options

SFSF 15OData API Functional GuideURI1URI2URI3URI4URI5URI6URI7URI8 orderbyYesYes topYes skipYes filterYesYes expandYesYesYes formatYesYesYesYesYesYesYes selectYesYesYesYes1.4.2 Comprehensive ExamplesIn this section, it demonstrates how to query with combination of system query options1.4.2.1Query with nested-navigation properties with selectUri a. http:// hostname /odata/v2/User? select userId,username,hr& format json& top 1Uri b.http:// hostname /odata/v2/User? select userId,username,hr/manager/proxy& format json& top 1 Identifies all entities of User. In response there wouldn’t show all properties of each entity, because select is given here. In each entity, only three selected properties would show in the result. Selectedproperty could be simple type or navigation, or even nested-navigation property. For simple typeproperties will show in result directly, for navigation property, it would show a value as a deferred link.Whatever depth the navigation property navigates, it always shows a deferred link for the navigationproperty. Therefore, uri a and uri b get the same result because they have selected exactly the same 2simple properties and one navigation property. top here to limit the size of response.Example response:{" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('mhuang1')", "type" : "SFOData.User"}, "userId" : "mhuang1", "username" : "mhuang", "manager" : {" deferred" : {"uri" : nager"}}}1.4.2.2Query with select and expandhttp:// hostname /odata/v2/User? select userId,manager/hr/manager& format json& expand manager/hr

SFSF 16OData API Functional Guide Identifies all entities of User entitySet. The response is quite similar to the previous link, but there isslight difference. If the navigation property gets expanded, then it shows as a flat object, otherwise, itshows as a deferred link. Selected nested-navigation property manager/hr/manager gets to be expanded,then it will show an object manager, and manger gets a nested object hr, because last manager ofselected nested-navigation property doesn't get expanded, then the hr has a deferred link namedmanager which refers to the real object. For the unselected properties of expanded object will betrimmed.Example response:{" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('rocky upsert JAM 200')", "type" : "SFOData.User"}, "userId" : "rocky upsert JAM 200", "manager" : {" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('rocky upsert JAM 201')", "type" : "SFOData.User"},"hr" : {" metadata" : {"uri" :"http://localhost:8080/odata/v2/User('rocky upsert b11 112 inline manager11')", "type" :"SFOData.User"},"manager" : {" deferred" : {"uri" :"http://localhost:8080/odata/v2/User('rocky upsert b11 112 inline manager11')/manager"}}}}}}1.4.2.3Query links with select and expandhttp:// hostname /odata/v2/User('rocky upsert JAM 200')/proxy? select userId,manager/hr/manager& format json& expand manager/hr It’s hard to expect what would return in response when it comes to you in the first glance. Here is asimple principle, uri part goes first, then select goes, last expand goes. Last uri part tells it will returnset entities of User/proxy. Then select tells unselected could should be trimmed, last expand will

SFSF 17OData API Functional Guide expand selected navigation property. Unselected column won’t be expanded even expand token isprovided.Example response: it shows that, User(‘rocky upsert JAM 200’) has four proxy, select properties areuserId, manager/hr/manager, because manager is null, so expand wont’ be executed.[{" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('rocky upsert JAM 197')", "type" : "SFOData.User"}, "userId" : "rocky upsert JAM 197", "manager" : null},{" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('rocky upsert JAM 198')", "type" : "SFOData.User"}, "userId" : "rocky upsert JAM 198", "manager" : null},{" metadata" : {"uri" : "http://localhost:8080/odata/v2/User('rocky upsert b11 112 inline proxy')", "type" :"SFOData.User"}

OData API Functional Guide 4 1.3 Query String Options 1.3.1 System Query Option System Query Options are query string parameters a client may specify to control the amount and order of the data that an OData service returns for the resource identified by the URI. The names of al