TDV Adapter for Couchbase

Build 22.0.8462

Automatic Schema Discovery

Child Tables

If the documents within a bucket contain fields with arrays, then the adapter 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 adapter 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 adapter 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 adapter 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 adapter 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 adapter 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 adapter 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"

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462