Closed
Description
This is to get handy in json in sql
here, tr
DROP TABLE IF EXISTS packages;
CREATE TABLE packages(datas JSON);
pydef py_pydeptree(s):
"get a json experience from pip"
import pipdeptree as pdt
pkgs = pdt.get_installed_distributions()
dist_index = pdt.build_dist_index(pkgs)
tree = pdt.construct_tree(dist_index)
result=pdt.render_json_tree(tree, indent=4).replace('\n', '\n') # no need to remove newline ?
return ("%s" % result);
INSERT INTO packages select py_pydeptree('hi');
-- select datas from packages;
--select json_type(datas) from packages;
--select atom, fullkey, value from packages, json_tree(packages.datas) where json_tree.type NOT IN ('object','array');
with x as (select json_extract(Value,'$.key') package
, json_extract(Value,'$.package_name') name
, json_extract(Value,"$.installed_version")version
, json_extract(Value,'$.dependencies') Dependancies
from packages , json_each(packages.datas))
select * from x
```