Postgres Cluster (citus): Range Query on nested jsonb column

Question

I am using postgres 9.5.4 configured as cluster using citus 5.2

The table has been sharded using below commands.

SELECT master_create_distributed_table('mytest', 'id', 'hash');
SELECT master_create_worker_shards('mytest', 16, 1);

From below sample json data stored in jsonb column, I would like to search records matching

o['mid'] > 7000

This is sample of one user's record. There will be millions of such users.

{   "uid": 14105529,   "o": [
    {
      "mid": 6551,
       "ac": 1913,
       "ip": "144.36.233.44",
       "adw": 5,
       "at": 133000,
       "ad": 151015,
       "aid": 0
     },
     {
       "mid": 7552,
       "ac": 1913,
       "ip": "144.36.233.44",
       "adw": 5,
       "at": 133000,
       "ad": 151015,
       "aid": 0
     },
     {
       "mid": 7553,
       "ac": 1913,
       "ip": "144.36.233.44",
       "adw": 5,
       "at": 133000,
       "ad": 151015,
       "aid": 0
    }   ] 
}

The below query throws error, where as it executes on un-sharded table.

select count(1) from mytest,jsonb_array_elements(data->'l') where (value->>'e')::bigint>7000;

ERROR: cannot perform distributed planning on this query

DETAIL: Complex table expressions are currently unsupported

Murat Tuncer Murat Tuncer · Accepted Answer · 2016-11-09T20:11:48

jsonb_array_elements(data->'l') is causing a CTE to be created which causes this query to get rejected, could you re-write your query to avoid joins ?

Postgres Cluster (citus): Range Query on nested jsonb column

1 Answers