Query Strings and Commands

Discussion in 'Census: Developer Announcements' started by Administrator, Dec 14, 2016.

  1. Administrator

    Administrator Administrator Staff Member

    The EverQuest II Data Feeds API (Census) runs on a Mongo Database system which is an optimized NoSQL solution. Users wanting to request data from Census do so with RESTful API calls. In other words, GET queries where each parameter either requests certain data or filters the resulting data to refine your results. As with any GET query, the first parameter must be preceded by a question mark (?) and subsequent parameters must each be preceded by an ampersand (&) character.

    For example, this query would return all level 80 items of type Weapon with a quality (tier) of MYTHICAL.

    http://census.daybreakgames.com/xml/get/eq2/item/?leveltouse=80&type=Weapon&tier=MYTHICAL

    Searching

    Field Values

    Each request for data may be filtered by the value of certain "fields". Each field in a query string corresponds to a particular field within a particular object within a collection (characters, spells, items, etc.).

    Within Census data, fields have a hierarchy or level structure. When accessing a field that is parented under another field, you may reference it by using a dot (".") notation. For example, considering the following data:​

    Code:
    "item": {
    
      "id": 4126241497,
    
      "typeinfo": {
    
        "equip_optional": 1,
    
        "name": "expendable",
    
        "casttime": 1.0
    
      }
    
    }

    In order to find all matches for the name "expendable" within typeinfo, one would use the following query string:

    ?typeinfo.name=expendable

    Numeric Fields
    When more than one search condition is provided within the query string, on a number, it acts as a boolean 'AND' operation. For instance, consider the following query string:

    ?id=]10&id=[50

    This request would return data where the value of the id attribute was between 10 and 50 (inclusive of 10 and 50).

    Text Fields
    When more than one search condition is provided within the query string on a text string, it acts as a boolean 'OR' operation. For instance, consider the following query string:

    ?given_by=racialtradition&given_by=focusabilities

    This request would return data where the value of the given_by attribute is EITHER racialtradition OR focusabilities.

    A complete list of search modifiers appears below:​

    CommandExampleDescription
    c:startc:start=10Start with the Nth object within the results of the query
    c:limitc:limit=20Limit the results to at most N objects
    c:showc:show=field,field Only include the provided field(s) from the objects returned

    c:countc:count=1 Include the total count of the queried results; compare to the "limit" to see if the results have been constrained by a limit. The value is either 1 or 0 (default is 0, or "don't show").

    c:hidec:hide=field,field Show everything EXCEPT for the provided field(s) from the objects returned

    c:sortc:sort=field[:1,:-1],field Sort the results by the field(s) provided; sort order specified after a ":" using 1 for ascending and -1 for descending
    c:hasc:has=fieldOnly include objects where the specified field(s) exist, regardless of the value within that field
    c:resolve c:resolve=field,field"Resolve" information by merge data from another collection using provided field(s)
    c:attachments c:attachments=categoryInclude the attachments for the object for the given category(s) within an attachment_files element
    c:distinctc:distinct=fieldReturn the distinct values of a particular field.

    c:explain c:explain=1"Explain" the database logic that is used for the query to analyze performance

    Regular expressions or "regex" provide a great deal of power for searching for objects. Census supports Perl-compatible regular expressions. The "/" and "^" modifiers above are actually examples of simple regular expressions. It is beyond the scope of this document to fully explain regexes, however there are a number of resources available to learn about them, including here, here and here.​

    NOTE: Use of i/string/ for case-insensitive searching of Character, Guild, Item, and Spell names is deprecated in favor of standard searches against their lowercase equivalents. Search performance will be substantially improved by using these:​
    • character.name.first_lower
    • item.displayname_lower
    • guild.name_lower
    • spell.name_lower

    Has

    The presence or absence of certain fields in data can be just as valuable as the fields themselves. For instance, the following query returns items that have the typeinfo.growthdescription field (eg. green adornments or Spirit Stones):

    ?c:has=typeinfo.growthdescription

    There is no separate "Has Not" or "Doesn't Have" query. Instead, if you need to find entries where certain fields are missing, add a ! in front of certain fields:

    ?c:has=typeinfo.growthdescription,!growth_table

    Distinct

    Sometimes you wish to know all the distinct values for a specific field. For instance, to find all possible qualities (tiers) of items:

    census.daybreakgames.com/xml/get/eq2/item/?c:distinct=tier

    NOTE: c:distinct may not have good performance on extremely large collections. It should be used to build internal cheat sheets/lists to work against. C:distinct is exceptionally slow when used against the Character collection.​

    Resolve
    Some collections have related data which may or may not be needed when accessing a collection. For instance, when you retrieve a Character, a stripped down list of Spells and Achievements are included, however the underlying data such as the name, description, and stats for these data points is not. If you wish to add this data to your query, you may use a c:resolve to "join" collections together.


    Example of Resolves for Guilds

    Before EQ2 started adopting GUIDs (globally unique IDs) for Characters and Guilds, the game used a combination of DBID and WorldID to specify a unique character or guild. The same DBID could exist on multiple servers, so you had to use both DBID and WorldID. When you perform a standard 'pull' of a Guild, you only get the DBID of each character. For example, a guild named Honor on the Permafrost server:

    census.daybreakgames.com/xml/get/eq2/guild/?name=^Honor&world=Permafrost

    Code:
    <member_list>
    <member dbid="208021" name="Idemu"/>
    <member dbid="221484" name="Gige"/>
    <member dbid="263553" name="Klehost"/>
    .
    </member_list>
    To also get the GUIDs of characters in a guild, you'll need to add a c:resolve:

    census.daybreakgames.com/xml/get/eq2/guild/?name=^Honor&world=Permafrost&c:resolve=members

    Code:
    <member_list>
    <member dbid="208021" id="867583601813" name="Idemu"/>
    <member dbid="221484" id="867583615276" name="Gige"/>
    <member dbid="263553" id="867583657345" name="Klehost"/>
    .
    </member_list>
    NOTE: Characters that have not logged in since Nov 2011 will not have a GUID.

    You can expand your c:resolve to load additional Character data besides the GUID as demonstrated below:

    census.daybreakgames.com/xml/get/eq2/guild/?name=^Honor&world=Permafrost&c:resolve=members(displayname,type)

    Code:
    <member_list>
    <member dbid="208021" displayname="Idemu (Permafrost)" id="867583601813" name="Idemu">
    <type alignment="0" birthdate_utc="1100033940" class="Ranger" classid="39" deity="None" gender="male" level="86" race="Iksar" raceid="10"/>
    </member>
    
    <member dbid="221484" displayname="Gige (Permafrost)" id="867583615276" name="Gige">
    <type alignment="0" birthdate_utc="1100190443" class="Guardian" classid="3" deity="None" gender="male" level="48" race="Gnome" raceid="5"/>
    </member>
    
    <member dbid="263553" displayname="Klehost (Permafrost)" id="867583657345" name="Klehost">
    <type alignment="0" birthdate_utc="1102637984" class="Wizard" classid="23" deity="None" gender="male" level="50" race="Half Elf" raceid="6"/>
    </member>
    .
    </member_list>
    The current list of resolves can be found by browsing Collections for <resolve>:
    • Character
      • Spells
      • Factions
      • AppearanceSlots
      • EquipmentSlots
      • Achievements
      • Warders
      • Statistics
      • Dungeon_Items
    • Guild
      • Members
    • World
      • Status
    NOTE: Resolves such as c:resolve=spells or c:resolve=achievements when performed against one or more Characters can take several seconds to perform and also generate a substantial amount of data. If you are feeling really lucky, you may do a c:resolve=all to return as much data as is possible.​

    Limiting Fields in a Resolve

    You can limit the returned data within a c:resolve by indicating certain fields in parentheses:

    census.daybeakgames.com/xml/get/eq2/character/?c:show=displayname,spell_list&c:resolve=spells(name,level,description,duration)

    Just as you can use the syntax above to show fields, you can add a ! in front of the query to hide fields instead:

    census.daybeakgames.com/xml/get/eq2/character/?c:show=displayname,spell_list&c:resolve=spells(!icon,!cost,!tier)​


    Controlling Output

    Once you have written your query, you may wish to control the output, specifying which fields are returned, and sorting the results so that you can page through them.

    Show

    Only include the specified field(s) in the returned data.
    ?c:show=displayname,name,account,guild,world,skills
    Hide

    Exclude certain field(s) from the returned data.
    ?c:hide=achievements,spells,stats
    Start

    If you are paging through items, spells, characters, etc. you can use c:start to start with the Nth object within the results of a query. It is recommended to include a c:sort whenever doing a c:start and c:limit to page through results. Otherwise results can change order and records will be missed.
    ?c:start=50
    Limit

    By default, any query will return a single result unless you specify a c:limit.

    ?c:limit=99

    NOTE
    : The maximum number of results you can request at once is hard limited to 100 if you choose not to register a Service ID with SOE.​
    Count

    The following query returns the number of level 95 characters:

    However you may want to perform a query AND get a count of the number of results in the same request. This query would return the first ten level 95 characters and also the number of level 95 characters found:

    ?type.level=95&c:limit=10&c:count=1
    Returns:

    Code:
    <character_list count="57695" limit="10" min_ts="1358361070.009923" returned="10" seconds="0.596140">
    <character dbid="1639738" displayname="?????? (Barren Sky)" id="1335736468794" last_update="1358361072.616647" playedtime="3154929" ts="1358361072.616647" version="1" visible="1">
    .
    .
    </character_list>
    Sort

    You can sort the results of your query with the c:sort modifier. You may sort by more than one field in ascending or descending order. For example, to sort ascending on field 1 and then descending on field 2:

    ?c:sort=field1:1,field2:-1
    For example, to get the 20 characters with the highest number of completed Collections, this query would be used:

    census.daybeakgames.com/xml/get/eq2/character/?c:sort=collections.complete:-1&c:limit=20&c:show=displayname,type.level,world
    To get a list of level 91 Grandmaster spell choices sorted Alphabetically:

    census.daybeakgames.com/xml/get/eq2/spell/?tier_name=Grandmaster&level=91&c:sort=name:1&c:show=name,tier_name,level&c:limit=100
    Reverse

    If you need to reverse the order of the returned results in a query, c:reverse allows you to do so. The main use for this is to allow you to sort by newest first, but show the results in chronological order in your browser. That way you can see the 25 newest characters, logs, etc in order from oldest to newest.

    ?c:reverse=1
    NOTE: This is largely deprecated in favor of adding a negative sign to a standard c:sort, for example:

    ?c:sort=ts:-1

    Attachments


    Attachments are images or other files which are "attached" to a specific field. Generally this is returned as a url path to download the file.

    ?c:attachments=all
    For EverQuest II, the valid categories for c:attachment are paperdoll, headshot, petpaperdoll. You may also specify all to return all available attachments. The format is generally a fully-qualified URL to the external file(usually an image) that can be used within that object with additional, useful metadata.​
    Explain (Performance)

    Census consists of a cluster of five MongoDB servers. Searching the database against fields that are indexed typically returns results quickly. For example character names, item names, guild names, etc. are all indexed. However most fields are not indexed and thus queries that search against those fields will be somewhat to considerably slower. You can determine if one of your queries is using an unindexed field with the c:explain parameter.

    ?c:explain=1
    If"cursor" is returned with a value of "BasicCursor", then the field you are querying is not indexed.

    If "cursor" is returned with a value of "BtreeCursor..." then you are using indexes.

    Every query, indexed or otherwise, returns the number of seconds that were required to run the query.​
    Case

    c:case has been removed and is only provided here for historical reasons. All requests are now case-sensitive. Use either the i/string/ format or the lowercase fields for fastest performance when searching.​
     
    Last edited by a moderator: Jul 18, 2017

Share This Page