CrateDB object column type: nested data-type issue

CrateDB
1

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:

%{
  "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:

{: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

2

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

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

Object property type definition can also be nested:

CREATE TABLE t01 (
    obj_col OBJECT(DYNAMIC) AS (
      type TEXT,
      value OBJECT(IGNORED)
      color TEXT,
      label TEXT
  )
)
1 Like
3

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 ?