JSON DB Connector

Create a Database Collection in SearchBlox

Access Admin Console
Log in to the SearchBlox Admin panel
Go to the Collections tab
Create New Collection
Click on “+” or “Create New Collection”
Choose “Database Collection” as the type
Configure Collection Settings
Name: Enter a unique collection name (for example: JSON_Data)
Access: Choose Public or Private
Encryption: Turn on if needed
Language: Select the content language (default is English)
Finalize Setup
Click Save to create the collection

JSON Sample Data

The given sample XML file is used to set up SQL queries and database connection (URL) details.

Steps to Test the DB Connector:

Download the XML file.
Save it in the required folder.
Follow the given steps to check whether the DB connector is working correctly.

{
    "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

Database Type Selection
From the dropdown list, choose JSON
Enter Database URL
Enter the database URL in the format shown below

jdbc:json:DataModel=Relational;URI=file_path.json;

File Path Specification
Enter the full (absolute) path to your JSON file
Make sure the SearchBlox service account has permission to access the file
Provide SQL Query
Enter the SQL query that retrieves data from the JSON file and stores it

Save the Settings
Click Save to store your configuration
Test the Connection
Click Test Database Collection to verify the settings
The database connection must be successful for indexing to work properly

📘
Configure SQL Query
To get data from the JSON file, you must provide a SELECT SQL query.

You must map one unique field to uid (this is required).
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 previous query, the fields are shown in a hierarchical structure (for example):
[people].[personal.name.first]
[people].[vehicles.maintenance.date]
Reference for select queries for Database JSON Connector
If your table contains more than 100 entries, use the LIMIT clause in your query to control how many documents are indexed.
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.