Do table field limits include sub objects?

CrateDB
1

Hello All,

Does the field limit of 1000 include fields within objects and sub-objects ?
I’m investigating the possibility of a dynamic object field, that contains N sub object fields with fields within that (there are a max of 12 subfields with NO 3rd level objects!)

e.g (very rough pseudo :slight_smile: )

...
field11 int,
field12 real,
...
misc OBJECT (DYNAMIC) {
           obj1 OBJECT (DYNAMIC){
                    field1 int,
                    field2 real,
                    ....
                    field12 smallint   ( up to 12 fields is the max for the subobject )
            },
           obj2 OBJECT (DYNAMIC) {
                    N1 int,
                    N2 smallint,
                    ....
                    N12 int
            },
           .... 
           obj32 OBJECT (DYNAMIC){
           etc,
           etc,
           }
          ....
}
ts timestamp

IF it does count to the max field amount, would setting a different object type help ?

Many thanks for your help
David.

2

Does the field limit of 1000 include fields within objects and sub-objects ?

Yes, any indexed field. OBJECT(DYNAMIC) automatically maps data types and indexes properties on ingestion. The OBJECT container itself doesn’t count towards the limit, but each of it’s properties and also nested properties.

obj OBJECT(DYNAMIC) AS (
   txt TEXT,
   val DOUBLE
)

=> 2 “columns” / fields

OBJECT(IGNORED) only indexes predefined columns i.e.

obj OBJECT(IGNORED) AS (
   txt TEXT,
   val DOUBLE
)

would not add new indexes if e.g. {"txt":"Hello, "val": 1.02, "nested": { "val_new": 2.3}} is inserted. While in the dynamic case, this would lead to:

obj OBJECT(DYNAMIC) AS (
   txt TEXT,
   val DOUBLE,
   nested OBJECT(DYNAMIC) AS (
      val_new DOUBLE
   )
)

=> 3 “columns” / fields (txt, val, nested)

1 Like
3

Hi

Thanks for the reply. :+1:

So if the fields were added to a dynamic object ( or sub object), but without indexes, would this help ?
Basically, we’re allowing our customers to add their own named parameters to a table under an object field.
They’ll not be querying for field specifics, but just retrieving the data

Thanks

4

I need to correct my previous statement. Every column that appears in the table schema contributes to the mapping.total_fields.limit .

cr> CREATE TABLE a (obj OBJECT) WITH ("mapping.total_fields.limit" = 5);
cr> INSERT INTO a (obj) VALUES ('{"a":1,"b":2,"sub":{"z":1}}');
cr> INSERT INTO a (obj) VALUES ('{"a":1,"b":2,"sub":{"x":1}}');
SQLParseException[Limit of total fields [5] in index [a] has been exceeded]

cr> SHOW CREATE TABLE doc.a;
+-----------------------------------------------------+
| SHOW CREATE TABLE doc.a                             |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."a" (              |
|    "obj" OBJECT(DYNAMIC) AS (                       |
|       "a" BIGINT,                                   |
|       "sub" OBJECT(DYNAMIC) AS (                    |
|          "z" BIGINT                                 |
|       ),                                            |
|       "b" BIGINT                                    |
|    )                                                |
| )                                                   |
| WITH (                                              |
|    "mapping.total_fields.limit" = 5                 |
| )                                                   |
+-----------------------------------------------------+

Basically, we’re allowing our customers to add their own named parameters to a table under an object field.

In this case I would recommend to use an OBJECT(IGNORED) (sub)column. This will take any valid object / json, but not index it. You can retrieve the whole data object and also query on properties, but the properties are not indexed, not stored in a columnar way and will not count towards the mapping.total_fields.limit.

cr> CREATE TABLE a (obj OBJECT AS (sub OBJECT(IGNORED))) WITH ("mapping.total_fields.limit" = 5);
cr> INSERT INTO a (obj) VALUES ('{"a":1,"b":2,"sub":{"x":1}}');
cr> INSERT INTO a (obj) VALUES ('{"a":1,"b":2,"sub":{"z":1}}');
cr> SHOW CREATE TABLE doc.a;
+-----------------------------------------------------+
| SHOW CREATE TABLE doc.a                             |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."a" (              |
|    "obj" OBJECT(DYNAMIC) AS (                       |
|       "sub" OBJECT(IGNORED),                        |
|       "a" BIGINT,                                   |
|       "b" BIGINT                                    |
|    )                                                |
| )                                                   |
| WITH (                                              |
|    "mapping.total_fields.limit" = 5                 |
| )                                                   |
+-----------------------------------------------------+
1 Like