I run this to get a sample record from CTAN:

curl -get -o pgf.json 'https://ctan.org/json/2.0/pkg/pgf'

Then json_pp pretty prints the file just fine, which I believe implies that it’s valid JSON. But sqlite3 cannot handle it:

select json_extract(value, '$.repository') from json_each(readfile('pgf.json'));
Runtime error: malformed JSON

Is my sqlite syntax bad, or is there a bug in sqlite?

As an experiment, I stripped the file down to just this:

{"bugs":"https://github.com/pgf-tikz/pgf/issues","repository":"https://github.com/pgf-tikz/pgf"}

In that case, the extraction worked but it still gives a malformed JSON error:

select json_extract(value, '$.repository') from json_tree(readfile('sample.json'));
https://github.com/pgf-tikz/pgf
Runtime error: malformed JSON

UPDATE: the problem was that json_each expects an array, which cannot be a single object. So it had to be formed as json_extract(readfile(…), '$.repository')