# How to access nested object keys

**URL:** <https://community.cratedb.com/t/how-to-access-nested-object-keys/2070>\
**Category:** CrateDB\
**Tags:** sql\
**Created:** [August 19, 2025, 10:15am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070 "2025-08-19T10:15:42Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![volker](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/volker/32/1412_2.png) [@volker](https://community.cratedb.com/u/volker)\
**Post date:** [August 19, 2025, 10:15am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/1 "2025-08-19T10:15:42Z")

</div>

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’] ❌

- UNNEST: Only works for arrays, not nested objects ❌

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

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [August 19, 2025, 10:38am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/2 "2025-08-19T10:38:38Z")

</div>

Hi Volker,

thank you for writing in.

> [@volker](#):
>
> shelly\_metrics

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?

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

> [@](#):
>
> ```auto
> UnsupportedFeatureException[Can’t handle Symbol [SimpleReference: fields[‘aenergy’][‘total’]]]
> 
> ```

Maybe you are hitting an edge-case bug?

Let me also reference introductory documentation at [Document Store - CrateDB: Guide](https://cratedb.com/docs/guide/feature/document/) 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.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [August 19, 2025, 10:54am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/3 "2025-08-19T10:54:19Z")

</div>

> [@volker](#):
>
> 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

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

> [@volker](#):
>
> I need the value `fields['aenergy']['total']`.

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.

```sql
SELECT fields['aenergy'] FROM shelly_metrics;

```

---

<div class="post-metadata">

**Author:** ![volker](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/volker/32/1412_2.png) [@volker](https://community.cratedb.com/u/volker)\
**Post date:** [August 19, 2025, 11:25am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/4 "2025-08-19T11:25:43Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![karynsaz](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/karynsaz/32/611_2.png) [@karynsaz](https://community.cratedb.com/u/karynsaz)\
**Post date:** [August 19, 2025, 11:52am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/5 "2025-08-19T11:52:08Z")

</div>

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:

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

---

<div class="post-metadata">

**Author:** ![volker](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/volker/32/1412_2.png) [@volker](https://community.cratedb.com/u/volker)\
**Post date:** [August 19, 2025, 11:57am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/6 "2025-08-19T11:57:16Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [August 19, 2025, 11:59am UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/7 "2025-08-19T11:59:46Z")

</div>

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

See:

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

```

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

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [August 19, 2025, 3:47pm UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070/8 "2025-08-19T15:47:23Z")

</div>

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

We can easily reproduce this with:

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

```

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

```

It’s the same situation as:

```auto
select t, t3 from t2 group by t3

```

```auto
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](https://github.com/crate/crate/issues/18283)