# CrateDB object column type: nested data-type issue

**URL:** <https://community.cratedb.com/t/cratedb-object-column-type-nested-data-type-issue/1300>\
**Category:** CrateDB\
**Created:** [November 30, 2022, 11:21am UTC](https://community.cratedb.com/t/cratedb-object-column-type-nested-data-type-issue/1300 "2022-11-30T11:21:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![BANDANA\_PANDEY](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/bandana_pandey/32/740_2.png) [@BANDANA\_PANDEY](https://community.cratedb.com/u/BANDANA_PANDEY)\
**Post date:** [November 30, 2022, 11:21am UTC](https://community.cratedb.com/t/cratedb-object-column-type-nested-data-type-issue/1300/1 "2022-11-30T11:21:22Z")

</div>

Hello everyone,

I have a column of type `object` in one of our table, and using this column we are dumping our raw data. But it fails in some cases where data is something like this:

```auto
%{
  "1205220070": %{
    "lastUpdated": "2022-11-15 10:03:00",
    "data": [
      %{
        "type": "pm25",
        "value": 104,
        "color": "#E08638",
        "label": "PM2.5"
      },
      %{
        "type": "pm10",
        "value": 138,
        "color": "#E1CB43",
        "label": "PM10"
      },
      %{
        "type": "aqi",
        "value": 247,
        "color": "#E08638",
        "label": "AQI"
      },
      %{
        "type": "co2",
        "value": 535,
        "color": "#17AF35",
        "label": "CO2"
      },
      %{
        "type": "temperature",
        "value": 26.3,
        "color": "#17AF35",
        "label": "Temperature"
      },
      %{
        "type": "humidity",
        "value": 58,
        "color": "#17AF35",
        "label": "Humidity"
      },
      %{
        "color": "#74CB40",
        "type": "viral_index",
        "value": 73,
        "label": "Viral Index"
      }
    ]
  },
  "1209210034": %{
    "lastUpdated": "2022-11-08 08:55:21",
    "data": [
      %{
        "type": "pm25",
        "value": 33,
        "color": "#74CB40",
        "label": "PM2.5"
      },
      %{
        "type": "pm10",
        "value": 51,
        "color": "#74CB40",
        "label": "PM10"
      },
      %{
        "type": "aqi",
        "value": 55,
        "color": "#74CB40",
        "label": "AQI"
      },
      %{
        "type": "co2",
        "value": 640,
        "color": "#17AF35",
        "label": "CO2"
      },
      %{
        "type": "voc",
        "value": 367,
        "color": "#E08638",
        "label": "TVOC"
      },
      %{
        "type": "temperature",
        "value": 28.9,
        "color": "#E1CB43",
        "label": "Temperature"
      },
      %{
        "type": "humidity",
        "value": 53,
        "color": "#17AF35",
        "label": "Humidity"
      },
      %{
        "color": "#17AF35",
        "type": "viral_index",
        "value": 88,
        "label": "Viral Index"
      }
    ]
  }

```

And the error it throws is this:

```auto
{:ok,
 %{
   "error" => %{
     "code" => 4000,
     "message" => "SQLParseException[mapper [data.1205220070.data.value] cannot be changed from type [float] to [long]]"
   }
 }}

```

But as per above mentioned error, I did notice that it is trying to infer the nested **value** type, but in our case **value** can be anything(float/integer/string/boolean)

Also, this particular object column internal structure is not fixed, it is dynamic. So it should hold any level of nested data.

Let me know if there is anything which can be done, to store this kind of raw data.

Any help would be much appreciated.

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:** [November 30, 2022, 1:05pm UTC](https://community.cratedb.com/t/cratedb-object-column-type-nested-data-type-issue/1300/2 "2022-11-30T13:05:48Z")

</div>

`OBJECT(DYNAMIC)` always does a dynamic mapping of properties. You might want to use `OBJECT(IGNORED)` instead an manually define indexed sub-properties.

```sql
CREATE TABLE t01 (
    obj_col OBJECT(IGNORED) AS (
      type TEXT,
      color TEXT,
      label TEXT
  )
)

```

Object property type definition can also be nested:

```sql
CREATE TABLE t01 (
    obj_col OBJECT(DYNAMIC) AS (
      type TEXT,
      value OBJECT(IGNORED)
      color TEXT,
      label TEXT
  )
)

```

---

<div class="post-metadata">

**Author:** ![BANDANA\_PANDEY](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/bandana_pandey/32/740_2.png) [@BANDANA\_PANDEY](https://community.cratedb.com/u/BANDANA_PANDEY)\
**Post date:** [December 1, 2022, 10:31am UTC](https://community.cratedb.com/t/cratedb-object-column-type-nested-data-type-issue/1300/3 "2022-12-01T10:31:55Z")

</div>

ohkk thanks alot @proddata for the solution.

Also is it possible to alter column type from `OBJECT(IGNORED)` to `OBJECT(DYNAMIC)` for already existing column, without adding a new column and copying the data from old column to new one ?