Child Tables

If the documents within a bucket contain fields with arrays, then the connector will expose those fields as their own tables in addition to exposing them as JSON aggregates on the main table. The structure of these child tables depends upon whether the array contains objects or primitive values.

Array Child Tables

If the arrays contain primitive values like numbers or strings, the child table will have only two columns: one called "Document.Id" which is the primary key of the document containing the array, and one called "value" which contains the value within the array. For example, if the bucket "Games" contains these documents:

/* Primary key "1" */
{
  "scores": [1,2,3]
}

/* Primary key "2" */
{
  "scores": [4,5,6]
}

The connector will build a table called "Games_scores" containing these rows:

Document.Id	value
1	1
1	2
1	3
2	4
2	5
2	6

Object Child Tables

If the arrays contain objects, the child table will have a column for each field that occurs within the objects, as well as a "Document.Id" column which contains the primary key of the document containing the array. For example, if the bucket "Games" contains these documents:

/* Primary key "1" */
{
  "moves": [
    {"piece": "pawn", "square": "c3"},
    {"piece": "rook", "square": "d5"}
  ]
}

/* Primary key "2" */
{
  "moves": [
    {"piece": "knight", "square": "f1"},
    {"piece": "bishop", "square": "e4"}
  ]
}

The connector will build a table called "Games_moves" containing these rows:

Document.Id	piece	square
1	pawn	c3
1	rook	d5
2	knight	f1
2	biship	e4

NewChildJoinsMode

Note that the above data model is not fully relational, which has important limitations for use-cases that involve complex JOINs or DML operations on child tables. The NewChildJoinsMode connection property exposes an alternative data model which avoids these limitations. Please refer to its page in the connection property section of the documentation for more details.

Flavored Tables

The connector can also detect when there are multiple types of documents within the same bucket, as long as TypeDetectionScheme is set to Infer or DocType and CouchbaseService is set to N1QL. These different types of documents are exposed as their own tables containing only the appropriate rows.

For example, the bucket "Games" contains documents which have a "type" value of either "chess" or "football":

/* Primary key "1" */
{
  "type": "chess",
  "result": "stalemate"
}

/* Primary key "2" */
{
  "type": "chess",
  "result": "black win"
}

/* Primary key "3" */
{
  "type": "football",
  "score": 23
}

/* Primary key "4" */
{
  "type": "football",
  "score": 18
}

The connector will create three tables for this bucket: one called "Games" which contains all the documents:

Document.Id	result	score	type
1	stalemate	NULL	chess
2	black win	NULL	chess
3	NULL	23	football
4	NULL	18	football

One called "Games.chess" which contains only documents where the type is "chess":

Document.Id	result	type
1	stalemate	chess
2	black win	chess

And one called "Games.football" which contains only documents where the type is "football":

Document.Id	score	type
3	23	football
4	18	football

Note that the connector will not include columns in a flavored table that are not defined on the documents in that flavor. For example, even though both the "result" and "score" columns are included on the base table, "Games.chess" only includes "result" and "Games.football" only includes "score".

Flavored Child Tables

It is also possible for a flavored table to contain arrays, which will become their own child tables. For example, if the bucket "Games" contains these documents:

/* Primary key "1" */
{
  "type": "chess",
  "results": ["stalemate", "white win"]
}

/* Primary key "2" */
{
  "type": "chess",
  "results": ["black win", "stalemate"]
}

/* Primary key "3" */
{
  "type": "football",
  "scores": [23, 12]
}

/* Primary key "4" */
{
  "type": "football",
  "scores": [18, 36]
}

Then the connector will generate these tables:

Table Name	Child Field	Flavor Condition
Games
Games_results	results
Games_scores	scores
Games.chess		"type" = "chess"
Games.chess_results	results	"type" = "chess"
Games.football		"type" = "football"
Games.football_scores	scores	"type" = "football"