How to query a JSON column by a nested key?

Question

I’m using GridDB 5.3 with the Java client to store IoT sensor readings. Each reading is stored in a collection, and one of the columns contains a JSON payload as a string.

Here’s a minimal Java example of how I’m inserting the data:

Properties props = new Properties();
props.setProperty("host", "127.0.0.1");
props.setProperty("port", "10001");
props.setProperty("clusterName", "defaultCluster");
props.setProperty("user", "admin");
props.setProperty("password", "admin");

GridStore store = GridStoreFactory.getInstance().getGridStore(props);

ContainerInfo info = new ContainerInfo();
info.setName("sensor_data");
info.setType(ContainerType.COLLECTION);
info.setColumnInfoList(Arrays.asList(
    new ColumnInfo("id", GSType.INTEGER),
    new ColumnInfo("timestamp", GSType.TIMESTAMP),
    new ColumnInfo("payload", GSType.STRING) // JSON data stored here
));
store.putContainer(info);

Collection<Integer, Row> col = store.getCollection("sensor_data", Row.class);

String json1 = "{\"deviceId\":\"A1\",\"temp\":22.5}";
String json2 = "{\"deviceId\":\"A2\",\"temp\":28.1}";

Row row1 = col.createRow();
row1.setInteger(0, 1);
row1.setTimestamp(1, new Date());
row1.setString(2, json1);

Row row2 = col.createRow();
row2.setInteger(0, 2);
row2.setTimestamp(1, new Date());
row2.setString(2, json2);

col.put(row1);
col.put(row2);

What I want is a query like:

Find all rows where payload.deviceId = "A2". Currently, the only way I’ve found is:

Select all rows
Parse the JSON in Java
Filter manually

This works but is slow with large datasets.

My question:

Is there a way in GridDB to filter rows directly by a nested JSON value inside a STRING column, without retrieving and parsing everything in the application?

I’m not looking for schema redesign advice right now — I just want to know if native querying of nested JSON is possible in GridDB, and if so, how to do it in Java.

Or * Select all rows * Filter with regex manually

Wladimir Diskowski
– Wladimir Diskowski

2025-08-13 15:14:40 +00:00
Commented Aug 13 at 15:14 — Wladimir Diskowski
– Wladimir Diskowski, Commented Aug 13 at 15:14

BingChuan · Accepted Answer · 2025-08-14 01:56:51Z

GridDB does not natively support querying nested JSON values directly within a STRING column. The current capabilities of GridDB for handling JSON payloads stored as strings do not include querying nested elements within the JSON. The approach you are currently using—selecting all rows, parsing the JSON in Java, and then filtering manually—is the typical method for dealing with JSON data stored as strings in GridDB.

If you require the ability to query nested JSON values efficiently, you may need to consider a different database system that has built-in support for JSON data types and allows querying of nested JSON elements directly, such as MongoDB. MongoDB, for example, provides powerful querying capabilities for JSON documents, including the ability to query nested fields.

In summary, with GridDB, you will need to handle JSON parsing and filtering within your application code, as native querying of nested JSON is not supported.

Collectives™ on Stack Overflow

How to query a JSON column by a nested key?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related