By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (ID INT, data JSON);
INSERT INTO test VALUES
(1 , '["section1","section2","section1","section3"]'),
(2 , '["section2","section4"]');
SELECT * FROM test;
| ID | data |
|---|---|
| 1 | ["section1","section2","section1","section3"] |
| 2 | ["section2","section4"] |
select
id,
JSON_LENGTH(JSON_SEARCH(data, 'all', 'section1')) section1,
JSON_LENGTH(JSON_SEARCH(data, 'all', 'section2')) section2,
JSON_LENGTH(JSON_SEARCH(data, 'all', 'section3')) section3,
JSON_LENGTH(JSON_SEARCH(data, 'all', 'section4')) section4
from test;
| id | section1 | section2 | section3 | section4 |
|---|---|---|---|---|
| 1 | 2 | 1 | 1 | null |
| 2 | null | 1 | null | 1 |
select
id,
JSON_CONTAINS(data, '"section1"') section1,
JSON_CONTAINS(data, '"section2"') section2,
JSON_CONTAINS(data, '"section3"') section3,
JSON_CONTAINS(data, '"section4"') section4
from test;
| id | section1 | section2 | section3 | section4 |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 0 |
| 2 | 0 | 1 | 0 | 1 |