How to query from "any"/"map" data type on Tarantool?

Question

Following example from this answer. If I created map without index, how to query the inner value of the map?

box.schema.create_space('x', {format = {[1] = {'id', 'unsigned'}, [2] = {'obj', 'map'}}})
box.space.x:create_index('pk', {parts = {[1] = {field = 1, type = 'unsigned'}}})
box.space.x:insert({2, {text = 'second', timestamp = 123}}
box.execute [[ SELECT * FROM "x" ]]
-- [2, {'timestamp': 123, 'text': 'second'}]

How to fetch timestamp or text column directly from SQL without creating index?

Tried these but didn't work:

SELECT "obj.text" FROM "x"
SELECT "obj"."text" FROM "x"
SELECT "obj"["text"] FROM "x"
SELECT "obj"->"text" FROM "x"

Answer 1

You can register a Lua function to call it from SQL. The first example from our SQL + Lua manual shows exactly what you asked.

A bit simplified version of the example to explain the idea:

box.schema.func.create('GETFIELD', {
    language = 'LUA',
    returns = 'any',
    body = [[
        function(msgpack_value, field)
            return require('msgpack').decode(msgpack_value)[field] 
        end]],
    is_sandboxed = false,
    param_list = {'string', 'string'},
    exports = {'SQL'},
    is_deterministic = true
})

After registration of the function you can call it from SQL:

tarantool> \set language sql
tarantool> select getfield("obj", 'text') from "x"
---
- metadata:
  - name: COLUMN_1
    type: any
  rows:
  - ['second']
...

tarantool> select getfield("obj", 'timestamp') from "x"
---
- metadata:
  - name: COLUMN_1
    type: any
  rows:
  - [123]
...

Differences from the example in the manual:

• No hack with the global variable, but no dot syntax ('foo.bar.baz').
• Exported only to SQL.
• The return type is 'any': so it can be used for, say, the numeric 'timestamp' field. Downside: 'any' is reported in the result set metainformation.

(The idea suggested by Nikita Pettik, my teammate.)