Marcelo Ochoa
May 6 · 4 min read

When you think about JSON in a Database probably you take into account storing, querying and manipulate your data, there is great blog post by Chris Saxon about How to Store, Query, and Create JSON Documents in Oracle Database, but JSON support open a window to talk with external sources such as Apache Solr or ElasticSearch, which are JSON native too.

To show that We will introduce to the Open Source Scotas Push Connector, which is available at GitHub. A big picture of this product is shown below:

Push Connector Architecture

It use the Oracle Index Extensible API (ODCI) to provide a new domain index which index your table using an external instance of Apache Solr. For example if you have a table like:

CREATE TABLE OLS_TUTORIAL (
ID VARCHAR2(30) PRIMARY KEY,
NAME VARCHAR2(400),
MANU VARCHAR2(4000),
CAT VARCHAR2(400),
FEATURES CLOB,
INCLUDES VARCHAR2(4000),
WEIGHT NUMBER,
PRICE NUMBER,
POPULARITY NUMBER,
INSTOCK CHAR(5), -- true or false
MANUFACTUREDATE_DT TIMESTAMP,
PAYLOADS VARCHAR2(4000),
STORE VARCHAR2(200));

with rows like:

insert into ols_tutorial values (
'SP2514N',
'Samsung SpinPoint P120 SP2514N - hard drive - 250 GB - ATA-133',
'Samsung Electronics Co. Ltd.',
'["electronics", "hard-drive"]',
'7200RPM, 8MB cache, IDE Ultra ATA-133 NoiseGuard, SilentSeek technology, Fluid Dynamic Bearing (FDB) motor',
NULL,
NULL,
92,
6,
'true',
TO_TIMESTAMP('2006-02-13T15:26:37Z','YYYY-MM-DD"T"HH24:MI:SS"Z"'),
NULL,
'35.0752,-97.032');

an index using Push Connector and Apache Solr 8 will look like:

CREATE INDEX TUTORIAL_PIDX ON OLS_TUTORIAL(ID) INDEXTYPE IS PC.SOLR PARAMETERS('{NormalizeScore:true,
LogLevel:"INFO",
Updater:"solr@8983",
Searcher:"solr@8983",
SolrBase:"solr/tutorial",
CommitOnSync:true,
SyncMode:"OnLine",
BatchCount:5,
LockMasterTable:false,
IncludeMasterColumn:false,
HighlightColumn:"name,features",
MltColumn:"name",
DefaultColumn:"_text_",
ExtraCols:"''id'' value id, ''cat'' value cat FORMAT JSON, ''name'' value name, ''features'' value features, ''manu'' value manu, ''includes'' value includes, ''price_f'' value price, ''popularity_i'' value popularity, ''inStock_b'' value trim(inStock) FORMAT JSON, ''manufacturedate_dt'' value manufacturedate_dt, ''store_p'' value store"}');

finally a query using this new domain index is simple as is:

SELECT id,price FROM OLS_TUTORIAL WHERE SCONTAINS(ID,'video AND price_f:[* TO 400]')>0

Note that not only the ID column of the table is being indexed, cat, name, features, etc. are also indexed into Apache Solr and then available at SCONTAINS operator, insert, update, delete or truncate operations at OLS_TUTORIAL table are automatically tracked and executed at the external Solr storage talking Oracle and Solr using HTTP/HTTPS with JSON syntax.

This is the importance of native JSON support, you can easily build and parse JSON documents to send/receive with external sources. Let look deeper into the Push Connector Source to see what I meaning.

Here part of SolrPushConnector-bdy.sql code, when dropping an index a delete by query operation is sent to Solr server, a PLSQL code which sent this message is:

ODCIIndexDrop method

note that, We make a JSON object with the delete by query syntax and its sent using the procedure PushConnectorAdm.CREATEREQUEST, it basically creates an HTTP connection with the JSON object as POST method body. Here debug information at .trc files:

POST http://solr:8983/solr/tutorial/update/json?wt=json&ident=on
{
"delete":
{ "query":"solridx:SCOTT.TUTORIAL_PIDX"},
"commit":
{"softCommit":"false"}
}

A little more complicated thing is the indexing process (bulk indexing in Solr words), this process is started at the create index or rebuild operations, this section of the code is:

rebuild process start first by deleting all rows of an existing index at Solr server, the above example, then it works in two different modes, Deferred, basically enqueuing all rowids of the table for inserting into an AQ queue (SolrPushConnector.enqueueChange(‘’’||prefix||’’’,RIDS,’’insert’’);) or doing a full scan query at the master table in batch of BatchCount rows parameter value, the query executed for above index creation syntax look like:

select json_object('rowid' value ''||L$MT.rowid ,
'solridx' value 'SCOTT.TUTORIAL_PIDX',
'id' value id,
'cat' value cat FORMAT JSON,
'name' value name,
'features' value features,
'manu' value manu,
'includes' value includes,
'price_f' value price,
'popularity_i' value popularity,
'inStock_b' value trim(inStock) FORMAT JSON,
'manufacturedate_dt' value manufacturedate_dt,
'store_p' value store
ABSENT ON NULL returning CLOB) L$MT$R
from SCOTT.OLS_TUTORIAL L$MT,SCOTT.TUTORIAL_PIDX$C C
where L$MT.rowid=C.rid

We are querying the rowid (document id in Solr syntax) and the rest of the columns defined by ExtraCols parameter of the index, all in syntax of Oracle json_object function, finally joining the table with a temporary table which hold the batch of rowids being indexed (TUTORIAL_PIDX$C). Above query will return batch of JSON objects data ready to be sent to update command of Solr, for example:

each batch of 5 rows is sent using HTTP/HTTPS to Apache Solr instance.

Sound complicate, yes, but it shows how JSON in the database open a new world of applications which interacts with NoSQL external storage, web services, etc. even if your are using PLSQL, feel free to download the complete code of this new Oracle Index or play with it using Docker using this guide “Push Connector 2.0 for Oracle XE 18c

Oracle Developers

Aggregation of articles from Oracle & partners engineers, Groundbreaker ambassadors, and the developer community on all things Oracle Cloud and its technologies. The views expressed are those of authors solely and do not necessarily reflect Oracle's. Contact @ypoirier

Marcelo Ochoa

Written by

https://apex.oracle.com/pls/apex/f?p=ACES:DIRECTORY:::::SEARCH:Marcelo+Ochoa

Oracle Developers

Aggregation of articles from Oracle & partners engineers, Groundbreaker ambassadors, and the developer community on all things Oracle Cloud and its technologies. The views expressed are those of authors solely and do not necessarily reflect Oracle's. Contact @ypoirier

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade