View doesn't support jsonb columns when union multiple tables

(Fangbox) #1

An exception occurs when I select query from a view which union multiple tables include jsonb columns. The operation information is as follows. Is this a bug in cockroach?

root> show create view testjson_view;
   table_name   |                                                               create_statement                                                                
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
  testjson_view | CREATE VIEW testjson_view (id, data) AS SELECT id, data FROM defaultdb.public.testjson UNION SELECT id, data FROM defaultdb.public.testjson2  
(1 row)

Time: 448.612929ms

root> show create table testjson;
  table_name |                create_statement                 
+------------+------------------------------------------------+
  testjson   | CREATE TABLE testjson (                         
             |     id INT NOT NULL,                            
             |     data JSONB NOT NULL,                        
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),  
             |     FAMILY "primary" (id, data)                 
             | )                                               
(1 row)

Time: 448.614054ms

root> select * from testjson;
  id |       data        
+----+------------------+
   1 | {"name": "zhbo"}  
(1 row)

Time: 1.783976321s

root> select * from testjson_view
                                                                           -> ;
pq: unable to encode table key: *tree.DJSON

(Ron Arévalo) #2

Hey @Bobo,

This is actually expected behaviour and a known limitation of CRDB. The reason behind the error is that CockroachDB does not currently key-encode JSON values. So even if you were to run:

SELECT id, data FROM defaultdb.public.testjson UNION SELECT id, data FROM defaultdb.public.testjson2

You’d get the same error, you can follow the two github issues here:

#35260 and #24436

Could you tell me a bit more about what you’re trying to accomplish, perhaps we can provide a suitable alternative.

Thanks,

Ron

(Fangbox) #3

Hi @ronarev

Thanks very much.

I wanted to use view to query data from different tables.

Maybe I can adjust the data structure to have a try.

(Raphael 'kena' Poss) #4

You can convert the JSON to string on both sides, then convert back from string to JSON.

Like this:

SELECT @1, @2::JSON FROM (
SELECT id, data::string FROM defaultdb.public.testjson 
UNION SELECT id, data::string FROM defaultdb.public.testjson2);

could you check if that helps?

(Fangbox) #5

@knz

It is a good idea except that I can’t use the convenience of the json format.

Thanks!