How to change the TTL for a row or how to purge old data in Oracle NoSQL Database?

Question

It seems that the command alter table TTL affects only rows that are written after that time; it has no impact on existing rows

How to apply TTL on older/ existing data? how can we purge? any suggestions

Answer 1

You are right. Alter table TTL affects only rows that are written after that time; it has no impact on existing rows

You can use the delete command to purge. See below a detailed example In this case, I would like to set the TTL to 1 hour

sql-> SELECT $u, remaining_hours($u) as hours  FROM user $u ;
{"u":{"id":1,"name":"Hello"},"hours":3}
{"u":{"id":3,"name":"Hello"},"hours":1}
{"u":{"id":2,"name":"Hello"},"hours":1}
3 rows returned
sql-> delete from user $u where remaining_hours($u)  > 1;
{"numRowsDeleted":1}
1 row returned
sql-> SELECT $u, remaining_hours($u) as hours  FROM user $u ;
{"u":{"id":2,"name":"Hello"},"hours":1}
{"u":{"id":3,"name":"Hello"},"hours":1}

you can also update the TTL for each row. But Multi-row update is not supported

UPDATE User $u SET TTL 1 hours WHERE id = 1;
UPDATE User $u SET TTL remaining_hours($p) + 3 hours WHERE id = 1;

and you have also the API to do this