By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH TableA AS (
SELECT 1 AS column1, 'label1' AS column2 UNION ALL
SELECT 2, 'label2'
),
TableB AS (
SELECT 1 AS Bcolumn1, 'value1' AS Bcolumn2, 'value4' AS Bcolumn3 UNION ALL
SELECT 1, 'value2', 'value5' UNION ALL
SELECT 2, 'value3', 'value6'
)
SELECT
a.column1,
a.column2,
'[' || GROUP_CONCAT('[' || b.Bcolumn2 || ',' || b.Bcolumn3 || ']') || ']' AS json
FROM TableA a
LEFT JOIN TableB b
ON b.Bcolumn1 = a.column1
GROUP BY
a.column1,
a.column2;
| column1 | column2 | json |
|---|---|---|
| 1 | label1 | [[value1,value4],[value2,value5]] |
| 2 | label2 | [[value3,value6]] |