|
|
|
|
|
|
JSON support in SQL provides the ability to read/write JSON field types mapped into VARCHAR (if the size is less than 64K) or LONGVARCHAR.
|
|
|
New c-treeDB Functions for JSON
New c-treeDB Error Code
CTDBRET_FIXJSON = 4168 /* JSON field cannot reside in fixed record portion */
New c-treeDB Data Type
typedef pTEXT CTJSON, ctMEM *pCTJSON;
|
|
|
JSON Data Type
To create a table that contains a CT_JSON field, specify CT_JSON as the field type in the DODA. When you add a record that contains a CT_JSON field value, store it in CT_4STRING format, with the first four bytes of the field containing the length of the JSON value, followed by the JSON value.
JSON Indexing
To create an index on a CT_JSON field, set the index attributes in the IIDX and ISEG structures that you pass to CREIFIL(). Before calling CREIFIL(), call the PUTKSEGDEF() function to set the CT_JSON index attributes that will be used for the CT_JSON index in the CREIFIL() call. The CT_JSON index attributes include the key segment length, the c-tree data type of the key value, and the JSON key name that is being indexed.
The PUTKSEGDEF() function can be used to specify CT_JSON index attributes for a call to CREIFIL():
Example
In this example, the supplied IFIL definition (pifil) has two indexes of one key segment each. The example code sets the first extended segment info pointer to NULL and the second to a ctKSEGDEF structure initialized with the CT_JSON index attributes.
ISEG myseg[] = {
{ 8, 8, SRLSEG},
{ 3, 4, SCHSEG}
};
IIDX myidx[] = {
{8, 0, NO, NO, 0, 1, myseg, "$ROWID$"},
{4, 0, NO, NO, 0, 1, myseg+1, "doc_id"}
};
IFIL mydat = {
"qajson",
-1,
20,
0,
ctFIXED | ctTRNLOG,
3,
0,
ctTRNLOG,
myidx
};
ctKSEGDEF sd;
pctKSEGDEF pksegs[2];
/* No extended segment info for first index. */
pksegs[0] = NULL;
/* Set the extended segment info for the second segment. */
memset(&sd,0,sizeof(sd));
sd.kseg_type = ctKSEG_TYPE_JSON;
/* use source type from schema */
sd.kseg_styp = ctKSEG_STYP_PROVIDED;
/* set key segment length */
sd.kseg_ssiz = 4;
/* set c-tree data type of key value */
sd.kseg_comp = CT_INT4;
/* set the json key name that is being indexed */
strcpy(sd.kseg_desc,"id");
pksegs[1] = sd;
rc = CREIFIL(pifil);
The name of the JSON key to index is stored in the kseg_desc field of the ctKSEGDEF structure. However, this field is limited to ctKSEGDLEN (32) bytes in the ctKSEGDEF structure definition. To overcome this limit, the ctKSEGcreidx usage of PUTKSEGDEF() accepts an array of pointers to ctKSEGDEF structures instead of an array of ctKSEGDEF structures so that an application can allocate a buffer to hold the CT_JSON index attributes that is larger than the ctKSEGDEF structure when the JSON key name exceeds the length of the field as defined in the ctKSEGDEF structure. As long as kseg_desc is NULL-terminated, the larger kseg_desc value can be passed to PUTKSEGDEF() in this manner.
The ctKSEGcreidx information that is passed to PUTKSEGDEF() is stored in the c-tree library until the next call to an ISAM level index create function: CREIFIL(), CREIFILX(), CREIFILX8(), PRMIIDX(), PRMIIDX8(), TMPIIDX(), TMPIIDXX(), TMPIIDXX8(). The recommended usage is to call PUTKSEGDEF() with filno of ctKSEGcreidx immediately before the index create call.
For an extended key segment type (kseg_type) of ctKSEG_TYPE_JSON, a call to GETKSEGDEF() is able to return to the caller a larger amount of ctKSEGDEF data than the size of the ctKSEGDEF structure. A call to GETKSEGDEF() for a kseg_type of ctKSEG_TYPE_JSON should set kseg_ssiz to the size of the supplied buffer. If GETKSEGDEF() finds that the data is larger than the supplied buffer size, it returns -VBSZ_ERR and returns the required length in the kseg_rsv1 field. For example:
ctKSEGDEF sd;
NINT rc,bufsiz;
sd.kseg_type = ctKSEG_TYPE_JSON;
sd.kseg_ssiz = bufsiz;
if ((rc = GETKSEGDEF(keyno,i,&sd)) < 0) {
if (rc == -VBSZ_ERR) {
/* Buffer too small. sd.kseg_rsv1 holds the required size. */
}
}
This new behavior of PUTKSEGDEF() and GETKSEGDEF() is intended to be used to send and receive attributes for other extended key segment types in the future.
JSON Error Code
Error code 1122:
JSON_KTFM_ERR: CT_JSON key transformation failed. Check sysiocod for details. See CTJSON_* definitions.
sysiocod values that may be returned in case of error 1122:
#define CTJSON_NOT_JSON -1038 /* Not a valid JSON object */
#define CTJSON_NOT_OBJECT -1039 /* Valid JSON, but not an object */
#define CTJSON_UNSUPPORTED_TYPE -1040 /* The specified key data type is not supported */
Limitations to JSON Key Segments
|
|
|
V11.8 and later added JSON as a new data type, called CT_JSON. It is physically stored in a table using the CT_4STRING format, which can support strings up to 2 GB in length.
It is available in all APIs with varying levels of support: SQL, NAV, ISAM, and Low-Level.
The NAV API allows you to index specific JSON properties using FairCom’s path language. This allows you to find records containing specific property values or ranges of property values. And it allows you to navigate records in sorted order using JSON property values.
In SQL and ISAM, you can read and write the string value stored in a JSON field but you cannot extract the values in JSON properties and treat them as columns. You cannot also use indexes created by the NAV API.
|
|
|
The REST API supports a JSON field type. c-treeDB also supports the ability to index JSON field contents.
To index a JSON field, specify the JSON field name in the "name" attribute and the key, type and length of the attribute that needs to be indexed using the "key", "type" and "size" attributes in the index definition.
Example:
{
"fields": [{
"name": "custinfo",
"key": "name",
"type": "string",
"size": 50
}],
"unique": false
}
When inputting a record with a JSON field, the JSON field value can be specified as a JSON type. This is the highly recommended method:
{
"custid": "1000",
"custinfo": {
"name": "Bryan Williams"
}
}
Although not recommended, when inputting a record with a JSON field, the JSON field value can be specified as a string representing the JSON to insert:
"{\"custom\":\"1000\",\"custinfo\":{\"name\":\"Bryan Williams\" }"
Below is an example on how to query the JSON content once indexed:
{
"find": {
"operator": ">=",
"fields": {
"custinfo": {"name":"a"}
}
},
"select": [
"custid",
"custinfo"
]
}
When creating a table with a JSON field, the field length specified is ignored and the JSON field content can be as large as 2GB.
JSON Types
Because JSON supports a very precise list of types, the following types are included in JSON support:
