Database Search

Database Search is one of the faceted search interfaces that is available for database collection which supports databases like MySQL, SQL Server, Oracle as well as for CSV and MongoDB collections. Using this you can build your database search and display the same in a few minutes.
In SearchBlox v10, database plugin is available by default to make customized searches to Database, CSV and MongoDB collections.

To access database plugin please use the following link in your build
https://localhost:8443/database/index.html

Prerequisite

Although the database plugin would work across all collections, it would be best for database, CSV and MongoDB collections. Therefore please make sure to create either of these collections to check out this plugin.

1906

Steps to use Database Plugin for Database and CSV Collection

Facet Filters

The default facets for filters such as file type, keyword and modified date can be found in the left-hand menu. After selecting each filter, the facets will appear selected with x mark to deselect the same. By clicking the x next to the facet, it can be removed from the search results as shown in the following image.

  • File Type lists the file types available in the search results for filtering.
  • Keywords lists the words that are the keywords of the search, other than the search term results.
  • Modified Date has five options ranging from week to year, along with customized date range.
    You can also add collection as a facet by making changes in ../database/facet.js
  • Collection lists the collections that have the search results for the given search term.
    Similarly, you can also add other fields from Database or CSV or MongoDB as facets.

Adding a Facet in Database search

  • SearchBlox also supports fields like Collection, Title, Description, Language, Author and Subject as facets.
  • facet.js is the configuration file that provides options that can be configured to provide search results as required. The path for the facet.js file, after placing the plugin folder in SearchBlox, is ../webapps/searchblox/database/facet.js.
  • To add a new facet filter, define the field name, display name and the size of the filter values to be shown in facet.js. Facet filters can be string, number or date types. Except for the predefined facets such as keywords, title, description, size or date, if you need to add more custom facets, you can add them in facet.js (example: facet field: "group").
  • To add or edit facets and filters, please make the changes to ../searchblox/database/facet.js file. This file would have the search-related configuration information which can be customized by the user.
  • If you need to a custom field as facet, then the field has to be mapped in jdbc.json for database collection, csv.json for CSV collection, mongodb.json for MongoDB and mapping.json for file and HTTP collection.
  • If you need to add a facet field for type number or date, the field needs to be updated in csv.json, jdbc.json, mongodb.json, amazonS3.json and mapping.json here: ../webapps/searchblox/WEB-INF/ for CSV, database, Mongodb, AWS collections and other collections respectively.
    ref: Custom Fields in Search

Fields in facets.js

The content of facet.js are as follows:

window.facets = {
    "facets": [{
            "field": "colname",
            "display": "Collection Name",
            "size": "10"
        },
        {
            "field": "contenttype",
            "display": "File Type",
            "size": "10"
        },
        {
            "field": "keywords",
            "display": "keywords",
            "size": "10"
        },
        {
            "field": "lastmodified",
            "display": "Last Modified",
            "dateRange": [{
                    "name": "Last 24 hours",
                    "calendar": "days",
                    "value": "1"
                },
                {
                    "name": "Past Week",
                    "calendar": "days",
                    "value": "7"
                },
                {
                    "name": "Past Month",
                    "calendar": "months",
                    "value": "1"
                },
                {
                    "name": "Past Year",
                    "calendar": "years",
                    "value": "1"
                }
            ]
        }
    ],
    "collection": [],
    "sortBtns": [{
            "field": "lastmodified",
            "display": "Sort by date"
        },
        {
            "field": "relevance",
            "display": "Sort by relevance"
        }
    ],
    "facetFiltersOrder": [
      "colname","keywords"
    ],
    "facetFiltersType": "AND",
    "sortDir": "desc",
    "matchAny": "off",
    "pageSize": "10",
    "showAutoSuggest": "true",
    "defaultCname": "",
    "adsDisplay": "true",
    "featuredResultsCount": "3",
    "predictSearch": "false",
    "predictResultSize": "3",
    "customDateDisplayText":"Custom",
    "customDateEnable":"true",
    "relatedQuery": false,
    "relatedQueryFields": {
        "apikey": "",
        "field": "content",
        "operator": "and",
        "limit": "5",
        "terms": "10",
        "type": "phrase",
        "col": ""
    },
    "dataToBeDisplayed": {
        "1": {
            "title": "Title",
            "description": "Description"
        },
        "other": {
            "description": "Description"
        },
        "displayAll": true
    },
    "tune": {
        "enable": "true",
        "tune.0": "5",
        "tune.1": "3",
        "tune.2": "2",
        "tune.3": "5",
        "tune.4": "180",
        "tune.5": "10"
    },
    "gridColumns": [
      {
          "name": "Id",
            "field": "@no"
      },
      {
          "name": "Title",
            "field": "title"
      },
      {
          "name": "Description",
            "field": "description"
      },
      {
          "name": "URL",
            "field": "url"
      },
      {
          "name": "Last modified",
            "field": "lastmodified"
      }
  ],
    "defaultType": "",
    "pluginDomain": ""
};

The description of the fields in the preceding js file is provided in the following table:

FieldDescription
facetsThe facet filters have to be added in this section. The default facet filters available in the plugin are collection name (colname), content type (contenttype), Keywords(keywords) and last modified date(lastmodified)

"field" represents the actual field name, provided in preceding brackets
"display" represents the display name in the plugin
"size" represents the number of values displayed in the filter for user selection
"daterange" represents the range for the date filter
collectionUsing this field you can specify/limit the collections to be used for this plugin. You can specifiy one or more collections in this field. The collection numbers must be specified as digits, if more than one collection they have to be separated by comma.
SortBtnsSort By field has to be provided here.
facetFiltersOrderOrder of facet filters to be displayed in plugin has to be specified here
sortDirThe direction of sort whether ascending or descending has to be specified here
pageSizeUsing this parameter the number of results per page can be controlled in faceted search
showAutoSuggestAutosuggest can be enabled or disabled using this parameter
defaultCnameThis is to specify default collection in which the search has to be made in the beginning
adsDisplayFeatured results can be enabled or disabled in search results using this
featuredResultsCountThe number of featured results to be displayed in the results can be specified here
predictSearchPredicted results can be enabled using this field
predictResultSizeParameters for predicted search can be specified here
customDateDisplayTextThis is for custom date label change in facet filters
customDateEnableThis is to enable or disable custom date
relatedQueryEnable or disable related query by giving true or false value for this field. By default it would be true
relatedQueryFieldsIf you want related queries in search results then specify the API key within the field. Also you can limit it based on collection by giving specific column number.
"apikey": "BFC82BE93A351C018CB27C32E1138047",
"field": "content",
"operator": "and",
"limit": "5",
"terms": "10",
"type": "phrase",
"col": ""
dataToBeDisplayedThis is specific for collections like csv and database where results have to appear in tabular format. The fields can be specified here.
If "displayAll" is true within this field all the data would be fetched from the results
tuneTune parameter for relevancy of search results. This can be enabled by giving "enable": "true", within this field
gridcolumnsThis is to specify the fields to be displayed in result grid. More details regarding the same is presented in the next section
defaultTypeif AND is specified then boolean operator AND would be used in searching multiple terms. If none is specified it would be OR search
pluginDomainThe domain of SearchBlox where the plugin has to fetch results

Configuring Result Grid for Database Plugin

This result grid can be configured for Database, CSV and MongoDB collections. For Database collections alone there would be a source attribute however we will not be using the same in our plugin. The fields can be directly used from Database collection similar to CSV and MongoDB collections.
Sample database results in JSON:

[
                        {
                "@no": "1",
                "@id": "1",
                "score": "100",
                "raw_score": "10.0",
                "college": "VIT",
                "col": "1",
                "no": "1",
                "keywords": " VIT 1 1 Amit Science",
                "indexdate": "01 Aug 2019 12:15:59 UTC",
                "description": [],
                "language": "en",
                "source":                 {
                   "college": "VIT",
                    "no": "1",
                    "name": "Amit",
                    "dept": "Science"
                },
                "dept": "Science",
                "title": "Amit",
                "sb_boost": "1.0",
                "url": "db?url=",
                "contenttype": "db",
                "uid": "c4ca4238a0b923820dcc509a6f75849b",
                "colname": "database",
                "old_uid": "1",
                "lastmodified": "01 Aug 2019 12:15:59 UTC",
                "alpha": [],
                "name": "Amit",
                "es_metadata_id": "1"
            },
                        {
                "@no": "2",
                "@id": "3",
                "score": "100",
                "raw_score": "10.0",
                "college": "MIT",
                "col": "1",
                "no": "3",
                "keywords": " MIT 3 3 Camila Biology",
                "indexdate": "01 Aug 2019 12:15:59 UTC",
                "description": [],
                "language": "en",
                "source":                 {
                    "college": "MIT",
                    "no": "3",
                    "name": "Camila",
                    "dept": "Biology"
                },
                "dept": "Biology",
                "title": "Camila",
                "sb_boost": "1.0",
                "url": "db?url=",
                "contenttype": "db",
                "uid": "eccbc87e4b5ce2fe28308fd9f2a7baf3",
                "colname": "database",
                "old_uid": "3",
                "lastmodified": "01 Aug 2019 12:15:59 UTC",
                "alpha": [],
                "name": "Camila",
                "es_metadata_id": "3"
            }]

Changes in facet.json grid columns based on the preceding sample data:

"gridColumns": [
       {
          "name": "No",
            "field": "no"
      },
      {
          "name": "Title",
            "field": "title"
      },
      {
          "name": "Department",
            "field": "dept"
      },
      {
          "name": "College",
            "field": "college"
      },
	   {
          "name": "Name",
            "field": "name"
      },
      {
          "name": "Last modified",
            "field": "lastmodified"
      }
    ]
}

Configuring the Data to be Displayed on Clicking Result

By default database plugin displays all fields. By default, displayAll is true which fetches all fields in the individual result display. This can be customized base on collection by specifying the collection number in the JSON code within the dataToBeDisplayed field. In the following code we have specified the field for collection no 1 and only the description field for other collections. Also, displayAll is disabled so only the three fields will appear in individual result display.

"dataToBeDisplayed": {
        "1": {
            "title": "Title",
            "dept": "Department",
			"college": "College"
        },
        "other": {
            "description": "Description"
        },
        "displayAll": false
    },

The result for the preceding settings would be as shown: