JSON DB Connector
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.jpg 1435](https://files.readme.io/6536dba-Json.jpg)
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
![jsonrawdatastructure.png 569](https://files.readme.io/8161f30-jsonrawdatastructure.png)
JSON Objects in the file
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.
![Json 2.jpg 1338](https://files.readme.io/2203c68-Json_2.jpg)
- 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 representated interms 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.
![Json 3.jpg 1423](https://files.readme.io/821f6e3-Json_3.jpg)
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.
Action | Description |
---|---|
Schedule | For each collection, indexing can be scheduled based on the above options. |
To know more about Database Collection refer Database Collection
![Schedule 1.jpg 1573](https://files.readme.io/6d2100e-Schedule_1.jpg)
Search results in faceted search is shown here:
![jsonresultJSON.png 1062](https://files.readme.io/e4d8944-jsonresultJSON.png)
![Json 7.jpg 1621](https://files.readme.io/4a44cbc-Json_7.jpg)
![XML 6.jpg 797](https://files.readme.io/227237e-XML_6.jpg)
Updated over 2 years ago