In PostgreSQL we can create a JSONB column that can be indexed and accessed something like this:
CREATE TABLE foo (
id BIGSERIAL PRIMARY KEY
-- createdAt, updatedAt, deletedAt, createdBy, updatedBy, restoredBy, deletedBy
data JSONB
);
CREATE INDEX ON foo((data->>'email'));
INSERT INTO foo(data) VALUES('{"name":"yay","email":"[email protected]"}');
SELECT data->>'name' FROM foo WHERE id = 1;
SELECT data->>'name' FROM foo WHERE data->>'email' = '[email protected]';
Which is very beneficial in the prototyping phase (no need for migration at all or locking when adding column).
Can we do similar thing in Tarantool?
Sure, tarantool supports JSON path indices. The example:
-- Initialize / load the database.
tarantool> box.cfg{}
-- Create a space with two columns: id and obj.
-- The obj column supposed to contain dictionaries with nested data.
tarantool> box.schema.create_space('s',
> {format = {[1] = {'id', 'unsigned'}, [2] = {'obj', 'any'}}})
-- Create primary and secondary indices.
-- The secondary indices looks at the nested field obj.timestamp.
tarantool> box.space.s:create_index('pk',
> {parts = {[1] = {field = 1, type = 'unsigned'}}})
tarantool> box.space.s:create_index('sk',
> {parts = {[1] = {field = 2, path = 'timestamp', type = 'number'}}})
-- Insert three tuples: first, third and second.
tarantool> clock = require('clock')
tarantool> box.space.s:insert({1, {text = 'first', timestamp = clock.time()}})
tarantool> box.space.s:insert({3, {text = 'third', timestamp = clock.time()}})
tarantool> box.space.s:insert({2, {text = 'second', timestamp = clock.time()}})
-- Select tuples with timestamp of the last hour, 1000 at max.
-- Sort them by timestamp.
tarantool> box.space.s.index.sk:select(
> clock.time() - 3600, {iterator = box.index.GT, limit = 1000})
---
- - [1, {'timestamp': 1620820764.1213, 'text': 'first'}]
- [3, {'timestamp': 1620820780.4971, 'text': 'third'}]
- [2, {'timestamp': 1620820789.5737, 'text': 'second'}]
...
JSON path indices are available since tarantool 2.1.2.