How to access nested object keys

CrateDB
1

Hi everyone,

I’m working with CrateDB 5.10.11 and I’m having trouble accessing nested object keys in my queries. I keep getting this error:

UnsupportedFeatureException[Can’t handle Symbol [SimpleReference: fields[‘aenergy’][‘total’]]]

My table structure:

I have a table called shelly_metrics with the following structure:

  • ts - timestamp when the record was created

  • measurement - string (I only need records with “shelly”)

  • tags - an object, I need records where tags[‘context’] = ‘status.switch:0’

  • fields - an object containing nested data, I need the value fields[‘aenergy’][‘total’] (watt) which is a float

What I’m trying to do:

I want to calculate the sum of watt differences for a given time period and a specific MAC address (tags[‘mac’]). The watt value is normally an increasing number, so I need to calculate the difference between consecutive records and sum those differences. If the previous value is greater than the next one, the difference should be 0, not a negative number.

The problem:

When I try to access fields[‘aenergy’][‘total’] directly in my query, CrateDB throws the error above. It seems like CrateDB doesn’t support nested object key access.

Example of what I’m trying to do:

SELECT
tags['mac'] as mac_address,
SUM(
CASE
WHEN LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
PARTITION BY tags['mac']
ORDER BY ts
) IS NULL THEN 0.0
WHEN CAST(fields['aenergy']['total'] AS DOUBLE) > LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
PARTITION BY tags['mac']
ORDER BY ts
) THEN
CAST(fields['aenergy']['total'] AS DOUBLE) - LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
PARTITION BY tags['mac']
ORDER BY ts
)
ELSE 0.0
END
) as total_watt_hours
FROM shelly_metrics
WHERE measurement = 'shelly'
AND tags['context'] = 'status.switch:0'
AND tags['mac'] = 'AA:BB:CC:11:22:33'
AND ts >= '2025-08-19T00:00:00Z'
AND ts <= '2025-08-19T23:59:59Z'
GROUP BY tags['mac'];

What I’ve tried:

  • Direct nested key access: fields[‘aenergy’][‘total’] :cross_mark:

  • UNNEST: Only works for arrays, not nested objects :cross_mark:

Sample data structure:

{
“ts”: “2025-08-19T10:00:00Z”,
“measurement”: “shelly”,
“tags”: {
"mac": "88:13:bf:d9:b1:5c", "context": "status.switch:0"
},
“fields”: {
"aenergy": {
"total": 1234.56
}
}
}

Questions:

  1. How can I access nested object keys like fields[‘aenergy’][‘total’] in CrateDB 5.10.11?
  2. Is there a specific syntax or function I should be using?
  3. Are there any workarounds for this limitation?

Any help would be greatly appreciated! Thanks in advance.

Volker.

1 Like
2

Hi Volker,

thank you for writing in.

Nice to hear you are processing data from Shelly appliances. Could you share the table definition (DDL) with us, so we could have a look at the schema together?

SHOW CREATE TABLE shelly_metrics;

In general, nested elements in OBJECT-type columns can be accessed like you are roughly doing it, but I admit I didn’t analyze your particular query statement yet.

Maybe you are hitting an edge-case bug?

Let me also reference introductory documentation at Document Store - CrateDB: Guide ff., if that may possibly help already to approach the problem from a different perspective, by convincing yourself the feature works in general, and build up from there?

In any case, please let us know if you can make progress or if you need us to have a more closer look by replicating the problem on our end. In this case, knowing about the table schema helps tremendously already.

With kind regards,
Andreas.

3

This feels familiar. It looks like this schema has been originating from an InfluxDB schema, at least deriving from its concepts?

By having a look at the actual database schema as CrateDB sees it, we can inspect how CrateDB currently stores fields['aenergy']. But you could also query for that field using a more basic statement to rule out query parsing edge cases (Can’t handle Symbol) you might be hitting.

SELECT fields['aenergy'] FROM shelly_metrics;
4

this is my current (over time auto growing) table schema:

CREATE TABLE IF NOT EXISTS "doc"."shelly_metrics" (
   "ts" TIMESTAMP WITHOUT TIME ZONE DEFAULT current_timestamp(3) NOT NULL,
   "measurement" TEXT NOT NULL,
   "tags" OBJECT(DYNAMIC) AS (
      "context" TEXT,
      "routing" TEXT,
      "mac" TEXT
   ),
   "fields" OBJECT(DYNAMIC) AS (
      "output" BOOLEAN,
      "apower" DOUBLE PRECISION,
      "current" DOUBLE PRECISION,
      "pf" DOUBLE PRECISION,
      "freq" DOUBLE PRECISION,
      "temperature" OBJECT(DYNAMIC) AS (
         "tF" DOUBLE PRECISION,
         "tC" DOUBLE PRECISION
      ),
      "aenergy" OBJECT(DYNAMIC) AS (
         "by_minute" ARRAY(DOUBLE PRECISION),
         "total" DOUBLE PRECISION,
         "minute_ts" BIGINT
      ),
      "id" BIGINT,
      "source" TEXT,
      "ret_aenergy" OBJECT(DYNAMIC) AS (
         "by_minute" ARRAY(DOUBLE PRECISION),
         "total" DOUBLE PRECISION,
         "minute_ts" BIGINT
      ),
      "voltage" DOUBLE PRECISION,
      "status" BOOLEAN,
      "connected" BOOLEAN,
      "errors" ARRAY(TEXT),
      "state" BOOLEAN,
      "unixtime" BIGINT,
      "ram_free" BIGINT,
      "restart_required" BOOLEAN,
      "reset_reason" BIGINT,
      "cfg_rev" BIGINT,
      "mac" TEXT,
      "uptime" BIGINT,
      "ram_size" BIGINT,
      "schedule_rev" BIGINT,
      "kvs_rev" BIGINT,
      "webhook_rev" BIGINT,
      "fs_size" BIGINT,
      "time" TEXT,
      "fs_free" BIGINT,
      "available_updates" OBJECT(DYNAMIC) AS (
         "stable" OBJECT(DYNAMIC) AS (
            "version" TEXT
         ),
         "beta" OBJECT(DYNAMIC) AS (
            "version" TEXT
         )
      ),
      "method" TEXT,
      "params" OBJECT(DYNAMIC) AS (
         "id" BIGINT,
         "on" BOOLEAN,
         "type" ARRAY(UNDEFINED)
      ),
      "src" TEXT
   ),
   "partition_field" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('month', "ts")
)
CLUSTERED INTO 1 SHARDS
PARTITIONED BY ("partition_field")
WITH (
   column_policy = 'strict',
   number_of_replicas = '0-1'
)

yes, this is inspired by influxDB, i had started with influx for a long time and using “flux” to define my queries. but after influx has canceled the support for flux i had searched for an alternative persistent storage for my IoT metrics. after some research , i finaly hit crateDB and i realy love it! easy to host, performance by default, and a HTTP interface to query. so this is my perfect match!

1 Like
5

Hi Volker,

I’ve tested it with a simpler table definition:
CREATE TABLE shelly_metrics (ts timestamp, measurement TEXT, tags OBJECT AS (mac TEXT, context TEXT), fields OBJECT AS (aenergy OBJECT AS (total FLOAT)));

And as Andreas shared, you can check that the sub-object is easily accessed with a simple query such as:
select CAST(fields[‘aenergy’][‘total’] AS DOUBLE) from shelly_metrics limit 100;

So, I’d say the error message is actually misleading in this case. What I did to workaround this is using a CTE as follows:

with test_cte as (
  select tags['mac'] as mac, (CASE
      WHEN LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
       PARTITION BY tags['mac']
        ORDER BY ts
      ) IS NULL THEN 0.0
      WHEN CAST(fields['aenergy']['total'] AS DOUBLE) > LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
       PARTITION BY tags['mac']
        ORDER BY ts
     ) THEN
        CAST(fields['aenergy']['total'] AS DOUBLE) - LAG(CAST(fields['aenergy']['total'] AS DOUBLE)) OVER (
         PARTITION BY tags['mac']
          ORDER BY ts
        )
      ELSE 0.0
    END) as flag
    FROM shelly_metrics 
  WHERE measurement = 'shelly'
  AND tags['context'] = 'status.switch:0'
  AND tags['mac'] = 'AA:BB:CC:11:22:33'
  AND ts >= '2025-08-19T00:00:00Z'
  AND ts <= '2025-08-19T23:59:59Z'
) select mac, sum(flag) from test_cte group by mac;

Could you give it a try and let me know if it works for you? My guess is that the combination of the SUM and the CASE don’t work well together, but I’d have to test it a bit more. It looks like a parsing error, actually.

Thank you,

Karyn Azevedo

2 Likes
6

okay, this is my current solution, and it running like a charm:

WITH energy_data AS (
  SELECT 
    ts,
    tags['mac'] as mac_address,
    CAST(fields['aenergy']['total'] AS DOUBLE) as energy_total
  FROM shelly_metrics 
  WHERE measurement = 'shelly'
    AND tags['context'] = 'status.switch:0'
    AND tags['mac'] = '88:13:bf:d9:b1:5c'
    AND ts >= '2025-08-19T00:00:00Z'
    AND ts <= '2025-08-19T23:59:59Z'
)
SELECT 
  mac_address,
  SUM(
    CASE 
      WHEN LAG(energy_total) OVER (ORDER BY ts) IS NULL THEN 0.0
      WHEN energy_total > LAG(energy_total) OVER (ORDER BY ts) THEN 
        energy_total - LAG(energy_total) OVER (ORDER BY ts)
      ELSE 0.0
    END
  ) as total_watt_hours
FROM energy_data
GROUP BY mac_address;

best regards, volker.

2 Likes
7

I think that the problem is that we do not support object access in the PARTITION_BY

See:

SELECT
 row_number()
 OVER (PARTITION BY tags['mac'])
FROM shelly_metrics 
GROUP BY fields['aenergy']['total'];

Error!
UnsupportedFeatureException[Can't handle Symbol [SimpleReference: tags['mac']]]

Edit: I was wrong, we do support object accessing in partition by, it’s just an expression, wouldn’t make any sense in our case that this wouldn’ be supported.

1 Like
8

The actual error is because tags['mac'] is not part of the group by.

We can easily reproduce this with:

CREATE TABLE "t2" (
  "t" TEXT,
  "t3" TEXT
);
SELECT row_number() OVER (PARTITION BY t) FROM t2 GROUP BY t3;

UnsupportedFeatureException[Can't handle Symbol [SimpleReference: t]]

It’s the same situation as:

select t, t3 from t2 group by t3

SQLParseException['t' must appear in the GROUP BY clause or be used in an aggregation function. Perhaps you grouped by an alias that clashes with a column in the relations]

What we should do is to improve the error message, that will be tracked at: Improve error message when column is not in the group by in window functions · Issue #18283 · crate/crate · GitHub

2 Likes