Editing Array in JSON like CSV with Power JSON Editor

ohdarling
3 min readAug 19, 2023

--

In front-end project development, we frequently utilize the JSON format as the communication medium between the server and front-end pages. JSON (JavaScript Object Notation), functioning as a lightweight data interchange format, is extremely user-friendly for reading and writing. Additionally, most request libraries are well-equipped to handle JSON response bodies effectively. Consequently, in the majority of scenarios, there is no need for us to perform any additional processing on JSON, allowing us to effortlessly read, edit, and utilize the JSON content returned by server APIs.

However, in scenarios where the dataset is relatively large, such as when a server API returns table data with numerous rows and columns, reading or editing this JSON data directly can become challenging. This is particularly true if the table contains a significant number of columns. In a standard text editor like Visual Studio Code, it’s difficult to effectively compare data in the same columns across different rows.

Example

For instance, consider the following JSON data returned by a server API:

{
"klines": [
{
"close": "472.000",
"open": "461.400",
"low": "459.000",
"high": "472.400",
"amount": "11429491",
"balance": "5356797004.000",
"turnover_rate": "",
"timestamp": "1643558400",
"factor_a": "1.0000",
"factor_b": "-19.730000",
"prev_close": ""
},
{
"close": "479.000",
"open": "483.000",
"low": "471.000",
"high": "483.000",
"amount": "15930509",
"balance": "7616780204.000",
"turnover_rate": "",
"timestamp": "1643904000",
"factor_a": "1.0000",
"factor_b": "-19.730000",
"prev_close": ""
},
// more than 300 records
]
}

This is a stock's candlestick (K-line) data, encompassing over 300 entries, with each entry containing 11 fields. If the objective is to rapidly observe the trend of the "open" field within the most recent 20 entries using Visual Studio Code, this becomes nearly impossible.

In such a situation, what's needed is a tool that simplifies the handling of this type of JSON data.

Solution

I’ve utilized the Power JSON Editor app to edit and manage JSON data of this type. This application serves as a JSON file editor, offering support for both source code and tree-view comparative editing of JSON files. It enables convenient editing of each node’s Key, type, and Value within the JSON file.

Power JSON Editor Main User Interface
Power JSON Editor Interface

The “klines” node within this sample data contains 311 child nodes, each sharing the same structure of 11 fields. To swiftly read and edit the data within these child nodes, we can employ the Array Table Editor plugin within Power JSON Editor. This functionality allows we to present these child nodes in a tabular format, greatly enhancing readability and ease of editing.

Upon selecting the “klines” node within the tree-view, navigate to the menu and choose Plugins > Array Table Editor > Edit Selected Node. This action will grant we access to the table editing plugin.

Access Array Table Editor plugin
Access Array Table Editor plugin

Now, we can read this structured JSON file in a manner akin to reading an Excel spreadsheet, and you have the ability to swiftly edit the data within each cell. It’s analogous to editing a CSV in Excel!

Array Table Editor

Absolutely, we can also leverage Power JSON Editor to export the data from this node as a CSV file, which can be employed for future data analysis, editing, presentation, and more. This approach allows we to fully harness the capabilities of software like Excel or Numbers, eliminating the need to grapple with the intricacies of the JSON format. To export this JSON node as a CSV, simply use the “Export as CSV” feature within the Array Table Editor plugin, enabling we to accomplish this task with a single click.

PS: Power JSON Editor is a paid software, so please decide whether to purchase based on your actual needs.

References

--

--