CREATE TABLE IF NOT EXISTS TestTable (Id int, JsonValue TEXT);
SELECT load_extension('json1.dll');
INSERT INTO TestTable (Id, JsonValue) values (1, '{"Header":[{"ArrayElement":"foo"},{"ArrayElement":"bar"}]}');
INSERT INTO TestTable (Id, JsonValue) values (2, '{"Header":[{"ArrayElement":"baz"},{"ArrayElement":"quux"}]}');

/* вот тут хочется получить на выход 3 строки вида:
1 foo
1 bar
2 baz
*/
--select Id, json_extract(JsonValue, ''$.Header.ArrayElement[]'') as ArrayElement from TestTable where length(ArrayElement)=3;

select Id, json_tree(JsonValue, '$.Header.ArrayElement') from TestTable where length(ArrayElement)=3;

SELECT load_extension('json1.dll');