4 ms·

Can this or anything else flatten arrays of JSON objects to CSV?

• TAForObvReasons 5y ago
  https://github.com/stedolan/jq https://github.com/stedolan/jq can manipulate arrays of objects and format as CSV
• lichtenberger 5y ago
  Of course you can do it with http://brackit.io http://brackit.io. What exactly is the "transformation" you envision?
  • lichtenberger 5y ago
    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 < $len) then ( $object=>$field || "," ) else ( $object=>$field || "\n" ) return string-join($value,"") will output: 0,tztz hello,null true,yes
    • lichtenberger 5y ago
      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.
    • kbrazil 5y ago
      You could do something like this in pure python without the json loading boilerplate with jello[0]. An interactive TUI for jello called jellex[1} is also available. (I am the author) [0] https://github.com/kellyjonbrazil/jello https://github.com/kellyjonbrazil/jello [1] https://github.com/kellyjonbrazil/jellex https://github.com/kellyjonbrazil/jellex
      • lichtenberger 5y ago
        JSON loading boilerplate? Is it also a compiler with sophisticated optimizations during compile time? :)
• screature2 5y ago
  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-should-know-for-flattening-json-13eae1dfb7dd 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).
  • dec0dedab0de 5y ago
    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 they used to vendorize dependencies, so everything worked out of the box, but now some features need to be installed specifically, or do pip install tablib[all], which is kind of annoying. I suspect they started doing it when they included support for pandas dataframes, because they didn't want to vendorize all of pandas. or force it to install as a requirement.
• m1sta_ 5y ago
  Excel can do this.
• Sn0wCoder 5y ago
  If you use JavaScript-> papaparse or like other have pointed out straight forward to do in python, bash, Perl, etc…
• digitalsanctum 5y ago
  Try https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron
• naan_bread 5y ago
  Try this: https://flatterer.opendata.coop/ 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_QKCu1WAEGQ0mEQ https://deepnote.com/@david-raznick/Flatterer-Demo-FWeGccp_Q...
• bewuethr 5y ago
  In addition to the already mentioned jq, there's https://github.com/jehiah/json2csv https://github.com/jehiah/json2csv
• DangitBobby 5y ago
  I mostly go from csv to csv but nushell handles table data in files really well [1]. I've done a little but of json manipulation with it but not a ton, so I can't say it's a silver bullet. 1. https://www.nushell.sh/book/ https://www.nushell.sh/book/
• jarofgreen 5y ago
  Here's a Python library with a CLI: Pypi: https://pypi.org/project/flattentool/ https://pypi.org/project/flattentool/ Docs: https://flatten-tool.readthedocs.io/en/latest/ https://flatten-tool.readthedocs.io/en/latest/ It's maintained by Open Data Services Coop, where we use it as a component in several of our web & data pipeline tools for working with data that is published in a Data Standard.
• mattewong 5y ago
  jq