R2Xj1dHH

1/*  
2This will result in error:
3  SQL Error [XX000]: ERROR: unable to encode table key: *tree.DJSON
4
5on insert
6*/  
7CREATE TABLE json_events2 (
8  uuid            STRING PRIMARY KEY,
9  value           JSONB,
10  event_type      STRING,
11  scenario_id     STRING,
12  conversation_id STRING,
13  account_id      STRING,
14  sequence_id     STRING,
15  unknown         JSONB,
16  timestamp       TIMESTAMP WITH TIME ZONE
17);
18
19
20CREATE INDEX IF NOT EXISTS json_debug_index  ON json_events2 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);
21
22
23ALTER TABLE json_events2 ADD COLUMN IF NOT EXISTS version INT;
24
25update json_events2
26set version = (unknown->>'scenarioIdVersion')::INT
27where version is null;
28
29insert into json_events2 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values 
30('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058') 
31
32
33/*
34  This will run ok
35*/
36
37CREATE TABLE json_events3 (
38  uuid            STRING PRIMARY KEY,
39  value           JSONB,
40  event_type      STRING,
41  scenario_id     STRING,
42  conversation_id STRING,
43  account_id      STRING,
44  sequence_id     STRING,
45  unknown         JSONB,
46  timestamp       TIMESTAMP WITH TIME ZONE
47);
48
49
50ALTER TABLE json_events3 ADD COLUMN IF NOT EXISTS version INT;
51
52update json_events3
53set version = (unknown->>'scenarioIdVersion')::INT
54where version is null;
55
56CREATE INDEX IF NOT EXISTS json_debug_index  ON json_events3 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);
57
58insert into json_events3 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values 
59('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058')