SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename ("test" TEXT);
INSERT INTO tablename ("test") VALUES
    ('[[''a'',''1''],[''b'',''2''],[''c'',''3'']]'),
    ('[[''a'',''2''],[''a'',''4''],[''c'',''8''],[''d'',''3'']]'),
    ('[[''b'',''3''],[''c'',5''],[''c'',''6''],[''d'',''4'']]');
WITH cte AS (
  SELECT rowid,
         SUBSTR(test, 1, INSTR(test || ';', ';') - 1) col,
         SUBSTR(test, INSTR(test || ';', ';') + 1) rest
  FROM (SELECT rowid, REPLACE(REPLACE(REPLACE(test, '[[', '['), ']]', ']'), '],[', '];[') test FROM tablename)
  UNION ALL
  SELECT rowid,
         SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
         SUBSTR(rest, INSTR(rest || ';', ';') + 1)
  FROM cte
  WHERE LENGTH(rest) > 0
)
SELECT GROUP_CONCAT(CASE WHEN col LIKE '[''a'',%' OR col LIKE '[''b'',%' THEN col END) x,
       GROUP_CONCAT(CASE WHEN col LIKE '[''c'',%' THEN col END) y,
       GROUP_CONCAT(CASE WHEN col LIKE '[''d'',%' THEN col END) z
FROM cte
GROUP BY rowid


x
y
z


['a','1'],['b','2']
['c','3']
null

['a','2'],['a','4']
['c','8']
['d','3']

['b','3']
['c',5'],['c','6']
['d','4']

x	y	z
['a','1'],['b','2']	['c','3']	null
['a','2'],['a','4']	['c','8']	['d','3']
['b','3']	['c',5'],['c','6']	['d','4']