Setting up JSON DB Connector

Create a Database Collection in SearchBlox

• After logging in to the Admin Console, select the Collections tab and click on Create a New Collection or "+" icon.
• Choose Database Collection as Collection Type.
• Enter a unique name for your collection (for example, JSON).
• Choose Private/Public Collection Access and Collection Encryption as per the requirements.
• Choose the language of the content (if the language is other than English).
• Click Save to create the collection.

JSON Sample Data

We will use the sample JSON data given in the following for configuring SQL queries and database URL string. You can download the following data as a JSON file, place it in a folder and follow the steps to test the DB connector.

{
    "people": [
      {
        "personal": {
          "age": 20,
          "gender": "M",
          "name": {
            "first": "John",
            "last": "Doe"
          }
        },
        "vehicles": [
          {
            "type": "car",
            "model": "Honda Civic",
            "insurance": {
              "company": "ABC Insurance",
              "policy_num": "12345"
            },
            "maintenance": [
              {
                "date": "07-17-2017",
                "desc": "oil change"
              },
              {
                "date": "01-03-2018",
                "desc": "new tires"
              }
            ]
          },
          {
            "type": "truck",
            "model": "Dodge Ram",
            "insurance": {
              "company": "ABC Insurance",
              "policy_num": "12345"
            },
            "maintenance": [
              {
                "date": "08-27-2017",
                "desc": "new tires"
              },
              {
                "date": "01-08-2018",
                "desc": "oil change"
              }
            ]
          }
        ],
        "source": "internet"
      },
      {
        "personal": {
          "age": 24,
          "gender": "F",
          "name": {
            "first": "Jane",
            "last": "Roberts"
          }
        },
        "vehicles": [
          {
            "type": "car",
            "model": "Toyota Camry",
            "insurance": {
              "company": "Car Insurance",
              "policy_num": "98765"
            },
            "maintenance": [
              {
                "date": "05-11-2017",
                "desc": "tires rotated"
              },
              {
                "date": "11-03-2017",
                "desc": "oil change"
              }
            ]
          },
          {
            "type": "car",
            "model": "Honda Accord",
            "insurance": {
              "company": "Car Insurance",
              "policy_num": "98765"
            },
            "maintenance": [
              {
                "date": "10-07-2017",
                "desc": "new air filter"
              },
              {
                "date": "01-13-2018",
                "desc": "new brakes"
              }
            ]
          }
        ],
        "source": "phone"
      }
    ]
  }

• The main object in this JSON file is people
• The structure of objects in the file is represented in the following table.
  Learn more on Parsing JSON Data

Database Collection Settings

• Select database type as JSON from the dropdown menu.
• Enter the database URL string in the format as shown here:
  jdbc:json:DataModel=Relational;URI=C:\xmljson\person.json;
• Provide the exact URI of your json file.
• Provide SQL Query, which fetches the data from JSON file and saves it.

• Save the settings.
• Check the settings by clicking Test Database Collection. The database connection has to be successful for the indexing to work.

📘

Configure SQL Query

• To fetch data from the JSON, a select SQL query must be provided.
• It is mandatory to map a unique field to uid
• The query should follow SQL SELECT query format with mappings. SQL Query for Database Collection
  SELECT <index field> as uid , <column name2> as title, <column name3> as content FROM <tablename> LIMIT 100
  Sample Query for JSON sample data:
  SELECT [people].[personal.name.first] AS uid, [people].[personal.name.last] AS title,[people].[personal.age] AS age,[people].[personal.gender] AS gender,[people].[vehicles.type],[people].[vehicles.model],[people].[vehicles.insurance.company],[people].[vehicles.maintenance.date] FROM [people]
• In the preceding query you can see the fields are represented in terms of hierarchy example:
  [people].[personal.name.first]
[people].[vehicles.maintenance.date]

Reference for select queries for Database JSON Connector

• If you have more than 100 entries in a table, specify the number of documents to be indexed by giving LIMIT value in the query. For example:
  SELECT <index field> as uid , <column name2> as title, <column name3> as content FROM <tablename> LIMIT 100
  Otherwise, a minimum of 50 rows will be indexed.

Schedule and Index

Sets the frequency and the start date/time for indexing a collection. Schedule Frequency supported in SearchBlox is as follows:

• Once
• Hourly
• Daily
• Every 48 Hours
• Every 96 Hours
• Weekly
• Monthly

The following operations can be performed in Database collections.

To know more about Database Collection refer Database Collection

Search results in faceted search is shown here: