12 lines
750 B
SQL
12 lines
750 B
SQL
|
ALTER TABLE inbox_responses ADD COLUMN options TEXT;
|
||
|
|
UPDATE inbox_responses SET options = json_array(option) WHERE option IS NOT NULL;
|
||
|
|
ALTER TABLE inbox_responses DROP COLUMN option;
|
||
|
|
|
||
|
|
UPDATE inbox_items SET payload = json_set(json_remove(payload, '$.recommended_option'),
|
||
|
|
'$.context', json_extract(payload, '$.context') || char(10) || char(10) || (
|
||
|
|
SELECT group_concat(CASE WHEN o.value = json_extract(inbox_items.payload, '$.recommended_option') THEN '- (*) ' ELSE '- ( ) ' END || o.value, char(10))
|
||
|
|
FROM json_each(inbox_items.payload, '$.options') o),
|
||
|
|
'$.multiple', json('false'),
|
||
|
|
'$.recommended', json_array(json_extract(payload, '$.recommended_option')))
|
||
|
|
WHERE kind = 'question' AND json_type(payload, '$.recommended_option') IS NOT NULL;
|