tarantool

PostgreSQL's JSONB-like indexable column in Tarantool?

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":"a@1.com"}');
SELECT data->>'name' FROM foo WHERE id = 1; 
SELECT data->>'name' FROM foo WHERE data->>'email' = 'a@1.com';

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?

Solution

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