How to do Graphql queries on elastic search with multiple logic conditions, sorting on multiple fields and also on date range

Question

We are using Sites9.5 version and indexing schema field values into elastic search (ES). We are trying to use graphql to query elastic search index. Currently SDL provides following four methods to do that: searchById searchByField searchByCriteria searchByRawQuery

We have three requirements which needs to be achieved in single graphql call:

1. query on multiple fields i.e. having multiple logical AND, OR conditions
2. sort the result on multiple fields
3. query on date range i.e. from date and to date. The date field is a custom date field on schema

Is there a way to achieve all these three conditions in single graphql query?

e.g. how graphql query will look like if we have to fulfill criteria like below: /search?query=path="//Building%20Blocks//Content//"+AND+metadata.componentstartdate="2019-01-01T00:00:00Z+TO+metadata.componentenddate=""2019-01-01T00:00:00Z"+OR+title="article"&?sort=field1:asc&sort=field2:desc&start=0&rows=10

Answer 1

Tridion Sites 9.5 Search - provided following Graphql API Queries

• searchById
• searchByField
• searchByCriteria
• searchByRawQuery

For your use case, you can try to use either searchByCriteria or searchByRawQuery. You could try playing around with these API GraphQL queries.

searchByCriteria option1:

Important to note about the creteria parameter API:

and: [InputSearchCriteria] Operation AND. This operation must be used together with a field and value or with a groupBy block. Operations can not be stacked to each other, but they can contain groupBy

field: String Field name for searching the index

groupBy: [InputSearchCriteria] Container group. This encapsulation must be used together with an operation and the container must contain an operation. This could be useful to form criteria which contain 2 or more operations.

strict: Boolean Parameter to enable strict search

or: [InputSearchCriteria] Operation OR. This operation must be used together with a field and value or with a groupBy block. Operations can not be stacked to each other, but they can contain groupBy

value: String Field value for searching the index

Example:

Query:

query searchByCriteria($first: Int, $after: String, $criteria: InputSearchCriteria!, $resultFilter: InputResultFilter, $inputSortParam: InputSortBy) {
  searchByCriteria(first: $first, after: $after, criteria: $criteria, resultFilter: $resultFilter, sortBy: $inputSortParam) {
    edges {
      cursor
      node {
        ...SearchResultFields
      }
    }
  }
}

fragment SearchResultFields on Result {
  hits
  searchResult {
    createdDate
    id
    itemType
    locale
    location
    mainContentField
    majorVersion
    minorVersion
    modifiedDate
    namespace
    publicationId
    publicationTitle
    rawContent
    rawLanguageTitle
    schemaId
    url
    highlighted
    fields
  }
  brokerResult {
    ... on Page {
      ...ItemFields
      rawContent {
        data
      }
      content {
        ... on UntypedContent {
          data
        }
      }
    }
  }
}

fragment ItemFields on Item {
  id
  itemId
  itemType
  namespaceId
  owningPublicationId
  publicationId
  title
  lastPublishDate
  creationDate
  initialPublishDate
  updatedDate
  ...CustomMetaFields
}

fragment CustomMetaFields on Item {
  customMetas {
    edges {
      node {
        id
        itemId
        key
        namespaceId
        publicationId
        value
        valueType
      }
    }
  }
}

Query Variable:

{
    "first": 10,
    "criteria": {
        "and": [
            {
                "field": "publicationId",
                "value": "5"
            }
        ],
        "and": [
            {
                "field": "namespace",
                "value": "tcm"
            }
        ],
        "and": [
            {
                "field": "itemType",
                "value": "page"
            }
        ],
        "field": "content+english",
        "value": "Lorem"
    },
    "resultFilter": {
        "highLightInAllIsEnabled": true,
        "highLightingIsEnabled": true
    },
    "inputSortParam": {
        "fields": [
            "createdDate"
        ],
        "sortAsText": false,
        "sortingDirection": "ASCENDING"
    }
}

searchByRawQuery Option2:

Example Query:

{
searchByRawQuery(rawQuery:"{\"type\": \"query\",\"op\": \"AND\",\"nodes\":  [{\"type\": \"field\",\"key\": \"content+english\",\"value\": \"Lorem\",\"fieldType\": \"STRING\"}, {\"type\": \"field\",\"negate\": \"false\",\"key\": \"namespace\",\"value\": \"tcm\",\"fieldType\": \"STRING\"} ]     }") {
      edges {
      cursor
      node {
        ...SearchResultFields
      }
    }
  }
}

fragment SearchResultFields on Result {
  hits
  searchResult {
    createdDate
    id
    itemType
    locale
    location
    mainContentField
    majorVersion
    minorVersion
    modifiedDate
    namespace
    publicationId
    publicationTitle
    rawContent
    rawLanguageTitle
    schemaId
    url
    highlighted
    fields
  }
  brokerResult {
    ... on Page {
      ...ItemFields
      rawContent {
        data
      }
      content {
        ... on UntypedContent {
          data
        }
      }
    }
  }
}

fragment ItemFields on Item {
  id
  itemId
  itemType
  namespaceId
  owningPublicationId
  publicationId
  title
  lastPublishDate
  creationDate
  initialPublishDate
  updatedDate
  ...CustomMetaFields
}

fragment CustomMetaFields on Item {
  customMetas {
    edges {
      node {
        id
        itemId
        key
        namespaceId
        publicationId
        value
        valueType
      }
    }
  }
}

Some reference tips for how to generate the raw query, originally the IQ Query Search service initially released for docs, and then it's extended to use for sites.

I would advise using either java maven search libraries or .NET Sdl.Tridion.Api.dll (Sdl.Tridion.Api.IqQuery.Model.Search) search API library for IQ Query Search Service to build and capture the Raw Query in fiddler and use it with Graphql Raw Query parameter.

Or alternative refers to the DXA Search module TridionDocsSearchController.cs to modify and build the query for your need.

IQ Query Search API Documentation

Tridion Sites 9.5 Search GraphQL API Sample Documentation

I hope it helps.

Answer 2

Note: sometimes you cannot use: A OR B AND C

In order to use that you have to add 1 more expression here to be like: A OR B AND C AND C