In addition to Automatic Schema Discovery the add-in also allows you to statically define the schema for your Couchbase object. Schemas are defined in text-based configuration files, which makes them easy to extend. You can call the CreateSchema stored procedure to generate a schema file; see Automatic Schema Discovery for more information.

Set the Location property to the file directory that will contain the schema file. The following sections show how to extend the resulting schema or write your own.

Example Document

Let's consider the document below and extract out the nested properties as their own columns:

/* Primary key "1" */
{
  "id": 12,
  "name": "Lohia Manufacturers Inc.",
  "homeaddress": {"street": "Main "Street", "city": "Chapel Hill", "state": "NC"},
  "workaddress": {"street": "10th "Street", "city": "Chapel Hill", "state": "NC"}
  "offices": ["Chapel Hill", "London", "New York"]
  "annual_revenue": 35600000
}
/* Primary key "2" */
{
  "id": 15,
  "name": "Piago Industries",
  "homeaddress": {street": "Main Street", "city": "San Francisco", "state": "CA"},
  "workaddress": {street": "10th Street", "city": "San Francisco", "state": "CA"}
  "offices": ["Durham", "San Francisco"]
  "annual_revenue": 42600000
}

Custom Schema Definition

<rsb:info title="Customers" description="Customers" other:dataverse="" other:bucket=customers"" other:flavorexpr="" other:flavorvalue="" other:isarray="false" other:pathspec="" other:childpath="">
  <attr name="document.id"        xs:type="string"  key="true" other:iskey="true" other:pathspec=""  />
  <attr name="annual_revenue"     xs:type="integer" other:iskey="false"           other:pathspec=""  other:field="annual_revenue" />
  <attr name="homeaddress.city"   xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.city" />
  <attr name="homeaddress.state"  xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.state" />
  <attr name="homeaddress.street" xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.street" />
  <attr name="name"               xs:type="string"  other:iskey="false"           other:pathspec=""  other:field="name" />
  <attr name="id"                 xs:type="integer" other:iskey="false"           other:pathspec=""  other:field="id" />
  <attr name="offices"            xs:type="string"  other:iskey="false"           other:pathspec=""  other:field="offices" />
  <attr name="offices.0"          xs:type="string"  other:iskey="false"           other:pathspec="[" other:field="offices.0" />
  <attr name="offices.1"          xs:type="string"  other:iskey="false"           other:pathspec="[" other:field="offices.1" />
  <attr name="workaddress.city"   xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.city" />
  <attr name="workaddress.state"  xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.state" />
  <attr name="workaddress.street" xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.street" />
</rsb:info>

In Custom Schema Example, you will find the complete schema that contains the example above.

Table Properties

The schema above uses the following properties to define specific qualities for the whole table. All of them are required:

Property	Meaning
other:dataverse	The name of the dataverse the dataset belongs to. Empty if not an Analytics view.
other:bucket	The name of the bucket or dataset within Couchbase
other:flavorexpr	The URL encoded condition in a flavored table. For example, "%60docType%60%20%3D%20%22chess%22".
other:flavorvalue	The name of the flavor in a flavored table. For example, "chess".
other:isarray	Whether the table is an array child table.
other:pathspec	This is used to interpret the separators within other:childpath. See Column Properties for more details.
other:childpath	The path to the attribute that is used to UNNEST the child table. Empty if not a child table.

Column Properties

The schema above uses the following properties to define specific qualities for each column:

Property	Meaning
name	Required. The name of the column, lower-cased.
key	Used to mark the primary key. Required for Document.Id but optional for other columns.
xs:type	Required. The type of the column within the add-in.
other:iskey	Required. Must be the same value as key, or "false" if key is not included.
other:pathspec	Required. This is used to interpret the separators within other:field.
other:field	Required. The path to the field in Couchbase.

Note that the fields which are produced by vertical flattening use the same syntax for separating array values and field values. This introduces a potential ambiguity in cases like the following, where the add-in exposes the columns "numeric_object.0" and "array.0":

{
  "numeric_object": {
    "0": 0
  },
  "array": [
    0
  ]
}

To ensure that the add-in can distinguish between field and array accesses, the pathspec is used to determine whether each "." in the field is an array or an object. Each "{" represents a field access, while each "[" represents an array access.

For example, with a field of "a.0.b.1" and a "pathspec" of "[{[", the N1QL expression "a[0].b[1]" would be generated. If instead the "pathspec" were "{{{", then the N1QL expression "a.`0`.b.`1`" would be generated.