Can this or anything else flatten arrays of JSON objects to CSV?
Excel can do this.
Select, put and delete data from JSON, TOML, YAML, XML and CSV files
11–20 of 34 posts
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#12Can this or anything else flatten arrays of JSON objects to CSV?
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#13Can this or anything else flatten arrays of JSON objects to CSV?
If you use JavaScript-> papaparse or like other have pointed out straight forward to do in python, bash, Perl, etc…
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#14Earlier quoted context omitted.
Of course you can do it with http://brackit.io . What exactly is the "transformation" you envision?
Something like this (very simplified): let $array := [{"foo":0,"bar":"tztz"},{"foo":"hello","bar":null},{"foo":true,"bar":"yes"}] let $value := for $object in $array return let $fields := bit:fields($object) let $len := bit:len($fields) for $field at $pos in $fields return if ($pos $field || "," ) else ( $object=>$field || "\n" ) return string-join($value,"") will output: 0,tztz hello,null true,yes
Of course you can simply create a module with something like the above snippet as a function for reuse. Or I can add a built-in function for flattening like this or as in pandas.
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#15I remember looking through XPath and wondering why no one had converted as much of it as possible to a generic path language for any tree-based data format, which JSON, TOML, YAML, and XML are.
XPath was the best of the XML standards. Well, it helps that the language wasn't xml unlike XSLT and others.
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#16I remember looking through XPath and wondering why no one had converted as much of it as possible to a generic path language for any tree-based data format, which JSON, TOML, YAML, and XML are. XPath was the best of the XML standards. Well, it helps that the language wasn't xml unlike XSLT and others.
Regarding XQuery we just added JSON querying on top in Brackit[1] / SirixDB[2].
Brackit is a retargetable query compiler and does a lot of optimizations at compile time as for instance optimizing joins and aggregations. It is useable as an in-memory processor or as a query processor of a database system.
The Ph.D. thesis of Sebastian:
Separating Key Concerns in Query Processing - Set Orientation, Physical Data Independence, and Parallelism
http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/publicatio...
[2] https://sirix.io
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#17Can this or anything else flatten arrays of JSON objects to CSV?
Try this: https://flatterer.opendata.coop/
There is no binary yet but there is a python CLI and library, even though it is written in rust.
It is the only tool that I know that deals with nested JSON and converts it into relational tables.
Here is a notebook of the python library usage.
https://deepnote.com/@david-raznick/Flatterer-Demo-FWeGccp_Q...
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#18Can this or anything else flatten arrays of JSON objects to CSV?
In addition to the already mentioned jq, there's https://github.com/jehiah/json2csv
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#19Can this or anything else flatten arrays of JSON objects to CSV?
I tend to use python (usually in a jupyter notebook) and pandas. Lots of experimentation with pandas' json_normalize() function. https://towardsdatascience.com/all-pandas-json-normalize-you... then just call to_csv() on the dataframe. (edited to add to comment on exporting to CSV as per the original question).
I also use jupyter for this, but my goto is tablib. for anyone who hasn't used it, it's super easy to switch between tabular data formats. you create an instance of their Dataset class, then assign your data to the appropriate property, and all of the other properties are your data in the respective format
for instance:
from tablib import Dataset
json_array_of_objects = '[{"header": "data1"}, {"header": "data2"}]'
ds = Dataset()
ds.json = json_array_of_objects
ds.csv # data formatted as a csv
ds.xlsx # excel, only useful on a binary read or write
ds.dict # list of dictionaries
ds.json # list of dictionaries converted to json
ds.jira # table formatted for jiras markup
ds.html # html table
# and more
Re: Select, put and delete data from JSON, TOML, YAML, XML and CSV files
#20I remember looking through XPath and wondering why no one had converted as much of it as possible to a generic path language for any tree-based data format, which JSON, TOML, YAML, and XML are. XPath was the best of the XML standards. Well, it helps that the language wasn't xml unlike XSLT and others.
XPath was a great system. However I feel like most of the features required existing XSLTs to support them.