JSON DB Connector

Create a Database Collection in SearchBlox

Access Admin Console
Log in to SearchBlox Admin
Navigate to Collections tab
Create New Collection
Click "+" or "Create New Collection"
Select "Database Collection" type
Configure Collection Settings
Name: Enter unique name (e.g., "JSON_Data")
Access: Select Public/Private
Encryption: Enable if required
Language: Select content language (default: English)
Finalize Setup
Click "Save" to create collection

JSON Sample Data

The provided sample XML data is used to configure SQL queries and database URL strings.

Steps to Test the DB Connector:

Download the XML file.
Place it in the designated folder.
Follow the specified steps to verify the DB connector functionality.

{
    "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 menu, select "JSON"
Enter the database URL string in the format as shown here:
```
jdbc:json:DataModel=Relational;URI=file_path.json;
```
File Path Specification
Provide the complete absolute path to your JSON file
Ensure proper file permissions for SearchBlox service account
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.