Comment by kara_jade

You can do this easily with the json_tree function from SQLite's JSON1 extension. It's given as an example in the documentation:

https://sqlite.org/json1.html#jtree

  SELECT big.rowid, fullkey, value
    FROM big, json_tree(big.json)
   WHERE json_tree.type NOT IN ('object','array');