Hello everyone,

I'm encountering an issue while working with Data model and SQL Transformation. I have a data model where I use a composite type to represent measurements. Here's a detailed description of my situation and the problem I'm facing.

Context

I want to model a type Metrics that includes two attributes, temperature and humidity. Each of these attributes is a composite type Measure, containing two sub-attributes: value and unit. Here is what my schema looks like in GraphQL:

enum Unit {
  Celsius
  Percent
}

type Measure {
  value: Float!
  unit: Unit!
}

type Metrics {
  temperature: Measure!
  humidity: Measure!
}

Problem

Although this model is syntactically correct in GraphQL, I am having difficulties loading this model using Cognite Data Fusion's SQL transformation. I haven't found a way to cast the unit and value fields into a Struct of type Measure.

Attempted SQL Transformation

I've tried several approaches to write the SQL transformation, but I can't seem to get the correct format. Here is an example of a transformation I attempted:

SELECT
  id,
  name,
  STRUCT(
    temperature_value AS value,
    temperature_unit AS unit
  ) AS temperature,
  STRUCT(
    humidity_value AS value,
    humidity_unit AS unit
  ) AS humidity
FROM
  my_data_table

Result

Despite my attempts, I receive type errors when loading the data, indicating that the temperature and humidity fields cannot be correctly cast into a Struct of type Measure.

Request for Help

I believe what I want to achieve is possible with GraphQL, but I haven't found how to perform this mapping in SQL Transformation. Has anyone encountered this problem before or have any suggestions on how to solve this SQL transformation issue?

Thank you in advance for your help!