Supported Rich Query Syntax

Oracle Blockchain Platform supports two types of rich query syntax that you can use to query the state database: SQL rich query and CouchDB rich query.

Topics:

SQL Rich Query Syntax

The Berkeley DB JSON extensions are in the form of SQL functions.

Before You Begin

Note the following information:

You can only access the channel chaincode (<STATE>) that you’re executing your query from.
Only the SELECT statement is supported.
You can’t modify the state database table.
A rich query expression can have only one SELECT statement.
The examples in this topic are just a few ways that you can write your rich query. You've access to the usual full SQL syntax to query a SQL database.
You've access to the JSON1 Extension (SQLite extension). See JSON1 Extension and SQL As Understood by SQLite.

If you need more information about writing and testing chaincodes, see Develop Chaincodes.

How to Refer to the State Database in Queries

The state database table name is internally managed by Oracle Blockchain Platform, so you don't need to know the state database’s physical name when you write a chaincode.

Instead, you must use the <STATE> alias to refer to the table name. For example: select key, value from <STATE>.

Note that the <STATE> alias is not case-sensitive, so you can use either <state>, <STATE>, or something like <StAtE>.

Retrieve All Keys

Use this syntax:

SELECT key FROM <STATE>

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following list of keys:

key

abg1234

abg1235

ser1236

bra1238

dtrt10001

Retrieve All Keys and Values Ordered Alphabetically by Key

Use this syntax:

SELECT key AS serialNumber, valueJson AS details FROM  <state> ORDER BY key

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

serialNumber	details
abg1234	{"docType": "vehiclePart", "serialNumber": "abg1234", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "airbag 2020", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
abg1235	{"docType": "vehiclePart", "serialNumber": "abg1235", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "airbag 4050", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
bra1238	{"docType": "vehiclePart", "serialNumber": "bra1238", "assembler": "bobs-bits", "assemblyDate": 1502688979, "name": "brakepad 4200", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
dtrt10001	{"docType": "vehicle", "chassisNumber": "dtrt10001", "manufacturer": "Detroit Auto", "model": "a coupe", "assemblyDate": 1502688979, "airbagSerialNumber": "abg1235", "owner": "Sam Dealer", "recall": false, "recallDate": 1502688979
ser1236	{"docType": "vehiclePart", "serialNumber": "ser1236", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "seatbelt 10020", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}

Retrieve All Keys and Values Starting with “abg”

Use this syntax:

SELECT key AS serialNumber, valueJson AS details FROM <state> WHERE key LIKE 'abg%'SELECT key, value FROM <STATE>

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

serialNumber	details
abg1234	{"docType": "vehiclePart", "serialNumber": "abg1234", "assembler": "panama-parts", "assemblyDate": "1502688979", "name": "airbag 2020", "owner": "Detroit Auto", "recall": "false", "recallDate": "1502688979"}
abg1235	{"docType": "vehiclePart", "serialNumber": "abg1235", "assembler": "panama-parts", "assemblyDate": "1502688979", "name": "airbag 4050", "owner": "Detroit Auto", "recall": "false", "recallDate": "1502688979"}

Retrieve All Keys with Values Containing a Vehicle Part Owned by "Detroit Auto"

Use this syntax:

SELECT key FROM <state> WHERE json_extract(valueJson, '$.docType') = 'vehiclePart' AND json_extract(valueJson, '$.owner') = 'Detroit Auto'

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following list of keys:

key

abg1234

abg1235

ser1236

bra1238

Retrieve Model and Manufacturer for all Cars Owned by "Sam Dealer"

Use this syntax:

SELECT json_extract(valueJson, '$.model') AS model, json_extract(valueJson, '$.manufacturer') AS manufacturer FROM <state> WHERE json_extract(valueJson, '$.docType') = 'vehicle' AND json_extract(valueJson, '$.owner') = 'Sam Dealer'

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

model	manufacturer
a coupe	Detroit Auto

CouchDB Rich Query Syntax

Use the information in this topic if you’re migrating your chaincodes containing CouchDB syntax to Oracle Blockchain Platform, or if you need to write chaincodes to install on Hyperledger Fabric peers participating in an Oracle Blockchain Platform network.

If you’re writing a new chaincode, then Oracle recommends that you use SQL rich queries to take advantage of the performance benefits that Oracle Blockchain Platform with Berkeley DB provides.

If you need more information about writing and testing chaincodes, see Develop Chaincodes.

Unsupported Query Parameters and Selector Syntax

Oracle Blockchain Platform doesn’t support the use_index parameter. If used, Oracle Blockchain Platform ignores this parameter, and it will automatically pick the indexes defined on the StateDB in question.

Parameter	Type	Description
use_index	json	Instructs a query to use a specific index.

Retrieve All Models, Manufacturers, and Owners of Cars, and Order Them by Owner

Use this expression:

{ 
  "fields": ["model", "manufacturer", "owner"], 
  "sort": [   
    "owner" 
   ]
}

Retrieve Model and Manufacturer for All Cars Owned by “Sam Dealer”

Use this expression:

{ 
  "fields": ["model", "manufacturer"], 
  "selector": {   
    "docType"  : "vehicle",
     "owner" : "Sam Dealer" 
  }
}