# Do table field limits include sub objects?

**URL:** <https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762>\
**Category:** CrateDB\
**Created:** [April 29, 2024, 8:15pm UTC](https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762 "2024-04-29T20:15:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [April 29, 2024, 8:15pm UTC](https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762/1 "2024-04-29T20:15:20Z")

</div>

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 🙂 )

```auto
...
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.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 30, 2024, 6:38am UTC](https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762/2 "2024-04-30T06:38:37Z")

</div>

> 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**.

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

```

=\> 2 “columns” / fields

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

```sql
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:

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

```

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

---

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [April 30, 2024, 8:26am UTC](https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762/3 "2024-04-30T08:26:24Z")

</div>

Hi

Thanks for the reply. 👍

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

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 30, 2024, 12:36pm UTC](https://community.cratedb.com/t/do-table-field-limits-include-sub-objects/1762/4 "2024-04-30T12:36:04Z")

</div>

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

```sql
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`.

```sql
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 |
| ) |
+-----------------------------------------------------+

```