Automatic Schema Discovery
Child Tables
If the documents within a bucket contain fields with arrays, then the driver 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 driver 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 driver 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 driver 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 driver 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 driver 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 driver 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" |