json數據轉換成表格

Like many of you, I often have to take the result of SQL queries and convert the rowsets to JSON data objects. Sometimes I have to do the same with CSV files from spreadsheets. The transformation process can be a hassle, though anyone can do it. Yet, it can be time-consuming and error-prone. This post will show you how to use the treeize Node.js package to simplify the process in very few lines of code.

像你們中的許多人一樣，我經常不得不獲取SQL查詢的結果并將行集轉換為JSON數據對象 。 有時，我必須對電子表格中的CSV文件執行相同的操作。 轉換過程可能很麻煩，盡管任何人都可以做到。 但是，這可能既耗時又容易出錯。 這篇文章將向您展示如何使用treeize Node.js包僅需幾行代碼即可簡化該過程。

Before going further, I’ll first need a dataset to base some examples on. The domain will be Books, which lend themselves to all sorts of categorization. I will use a fake data generator called casual, which I previously used for mocks in my post on GraphQL testing.

在繼續之前，我首先需要一個數據集以一些示例為基礎。 領域將是Books ，這使它們可以進行各種分類。 我將使用一個稱為Casual的偽造數據生成器，我先前在GraphQL測試中的帖子中使用過該模型 。

The book data will be of the following structure:

圖書數據將具有以下結構：

casual.define('book', () => {const author = casual.random_element(authors);const book = {first_name: author.first,last_name: author.last,title: casual.random_element(author.titles),category: casual.random_element(author.category)}return book; });

Every time I request a casual.book I get a book with a new set of values. It’s not entirely random. The generator uses some predefined data for well-known authors, and more-or-less randomly generated data for other authors. Here’s a sample:

每次我請求casual.book我都會得到一本casual.book新值的書。 這不是完全隨機的。 生成器為知名作者使用一些預定義的數據 ，為其他作者使用或多或少隨機生成的數據。 這是一個示例：

{ dataset:[ { first_name: 'Barbara',last_name: 'Cartland',title: 'The Pirate and the Piano Teacher',category: 'thriller' },{ first_name: 'Carlie',last_name: 'Haley',title: 'Digitized Global Orchestration',category: 'engineering' },{ first_name: 'Arthur',last_name: 'Doyle',title: 'The Case of the Spotted Dick',category: 'mystery' },{ first_name: 'Reinhold',last_name: 'Gutmann',title: 'Managed Directional Benchmark',category: 'management' },{ first_name: 'Isaac',last_name: 'Asimov',title: 'Once in a Venusian Sun',category: 'science fiction' },{ first_name: 'R. L.',last_name: 'Stein',title: 'Why are You Scared of Me?',category: 'childrens books' },{ first_name: 'Alicia',last_name: 'Cruickshank',title: 'Balanced Local Database',category: 'engineering' },{ first_name: 'Chase',last_name: 'Runte',title: 'Ergonomic Tertiary Solution',category: 'engineering' } ] }

If you’re interested in how this data was generated, the full source code used in this post can be found here. For a little bit of added realism, this generated data will be thrown into an in-memory SQL database for later retrieval. Here’s the format of the results for the SQL query:

如果您對如何生成這些數據感興趣，可以在此處找到本文中使用的完整源代碼。 為了增加一點真實感，此生成的數據將被扔到內存中SQL數據庫中，以便以后進行檢索。 這是SQL查詢結果的格式：

SELECT title, category, first_name, last_name FROM book JOIN author ON author.id = book.author

This format is, for all intents and purposes, identical to the format of the dataset shown just previously, for example:

出于所有目的和目的，此格式與之前顯示的數據集的格式相同，例如：

[ { title: 'Proactive Regional Forecast',category: 'mystery',first_name: 'Arthur',last_name: 'Doyle' },{ title: 'More Scary Stuff',category: 'suspense',first_name: 'Steven',last_name: 'King' },{ title: 'Scary Stuff',category: 'occult',first_name: 'Steven',last_name: 'King' },{ title: 'Persistent Neutral Info Mediaries',category: 'management',first_name: 'Maegan',last_name: 'Frami' },{ title: 'Enhanced Background Frame',category: 'engineering',first_name: 'Winifred',last_name: 'Turner' },...

The main difference between the dataset and the rowset is that when populating the database from the casual-generated data, I eliminated duplicate authors (by name) and book titles (by category):

數據集和行集之間的主要區別在于，從臨時生成的數據填充數據庫時，我消除了重復的作者(按名稱)和書名(按類別)：

轉換為JSON (Converting to JSON)

You might notice that the dataset results were in JSON format already. What this post aims for, though, is to build a containment hierarchy that shows the relationships between authors, books, and categories in a concise way. That’s not the case with the rowset values, where the results are glorified key-value pairs, where each pair is a column name and value from a table row.

您可能會注意到數據集結果已經是JSON格式。 但是，這篇文章的目的是建立一個包含層次結構，以簡潔的方式顯示作者，書籍和類別之間的關系。 行集值不是這種情況，其中的結果是美化的鍵值對，其中每個對都是列名和表行中的值。

So, for example, say I want to list authors, the categories they write in, and the titles of books in those categories that they authored. I want to show each category just once, and each book within each category should be listed only once, also.

因此，例如，說我想列出作者，他們所寫的類別以及他們所創作的那些類別中的書名。 我只想顯示每個類別一次，并且每個類別中的每一本書也應該只列出一次。

This is a pretty common type of reducing operation that is often applied to rowset data. One way to conquer the problem is to declare a container object, then populate it by looping through the rowsets. A typical implementation might be:

這是一種非常常見的歸約操作類型，通常應用于行集數據。 解決問題的一種方法是聲明一個容器對象，然后通過遍歷行集來填充它。 典型的實現可能是：

The handrolled()method gets a bit hairy the deeper the hierarchy. Local variables are used to reduce long path lengths. We have to keep the meta-structure in mind to write the proper initializations of properties in the JSON object. What could be simpler?

handrolled()方法在層次結構越深時變??得有些毛茸茸。 局部變量用于減少長路徑長度。 我們必須牢記元結構，以便在JSON對象中編寫屬性的正確初始化。 有什么可能更簡單？

The results returned are:

返回的結果是：

..."Doyle,Arthur": {"categories": {"thriller": {"titles": ["The Case of the Spotted Dick","The Case of the Mashed Potato"]},"mystery": {"titles": ["The Case of the Spotted Dick"]}}},"Asimov,Isaac": {"categories": {"science": {"titles": ["Once in a Venusian Sun","Total Multi Tasking Forecast"]},"general interest": {"titles": ["Total Multi Tasking Forecast","Once in a Venusian Sun","Fourth Foundation"]}}},"Kilback,Bradley": {"categories": {"management": {"titles": ["Mandatory Solution Oriented Leverage"]},"engineering": {"titles": ["Multi Layered Fresh Thinking Framework","Total Scalable Neural Net","Mandatory Solution Oriented Leverage"]},"reference": {"titles": ["Multi Layered Fresh Thinking Framework"]}}},...

用Treeize構建一棵樹 (Building a tree with Treeize)

The npm module treeize is designed to simplify the conversion of rowsets to structured JSON data through the use of descriptive keys. Installation through npm is per usual:

npm模塊樹化 旨在通過使用描述性鍵簡化行集到結構化JSON數據的轉換。 通常通過npm安裝：

npm install --save treeize

JSON行集 (JSON Rowsets)

Treeize is able to recognize reoccurring patterns in the rowsets. It transforms them according to how the key names are defined in metadata passed in as the seed structure. Here’s the code:

Treeize能夠識別行集中的重復模式。 它根據如何在作為種子結構傳入的元數據中定義關鍵字名稱來對其進行轉換。 這是代碼：

This is about a dozen lines of code compared to double that for the hand-rolled version. Notice the key values used in the mapping operation. Treeize recognizes plurals as collections, so categoriesand titleswill be arrays. The colons (‘:’) in the names indicate nesting. Typewill be a property of an object in the array of categories, and namewill be a property in all objects in titles.

與手動版本相比，這大約是十幾行代碼。 請注意映射操作中使用的鍵值。 Treeize將復數形式識別為集合，因此categories和titles將是數組。 名稱中的冒號('：')表示嵌套。 Type將是類別數組中對象的屬性， name將是標題中所有對象的屬性。

The tree is built when authors.grow(seed) is called, and the results retrieved through authors.getData(). However, it doesn’t quite yield the same results as what we had from the hand-rolled method:

該樹是在調用authors.grow(seed)并通過authors.getData()檢索結果時構建的。 然而，它并不完全產生相同的結果，我們從手卷方法有：

..., {"name": "Glover, Ashley","categories": [{"type": "engineering","titles": [{"name": "Intuitive Full Range Capacity"},{"name": "Organic Encompassing Core"}]},{"type": "reference","titles": [{"name": "Distributed Client Server Service Desk"},{"name": "Organic Encompassing Core"}]},{"type": "management","titles": [{"name": "Organic Encompassing Core"}]}] },...

One notable difference is that categories are not named objects (as before), but objects with a name property. Title is also not just an array of strings, but an array of objects with nameas the title. Treeize interprets categories and titles as arrays of objects, not as maps (or arrays of primitives). For most use cases, this is not much of an issue. But, if you need to find a category by name quickly (rather than iterate through an array of categories), then you can take care of that through a couple of reduce operations to arrive at the same structure as before:

一個顯著的區別是類別不是像以前一樣命名的對象，而是具有name屬性的對象。 Title不僅是一個字符串數組，而且是一個以name為標題的對象數組。 Treeize將categories和titles解釋為對象數組，而不是地圖(或基元數組)。 對于大多數用例來說，這不是什么大問題。 但是，如果您需要按名稱快速查找類別(而不是遍歷一系列類別)，則可以通過幾次歸約操作來達到與以前相同的結構：

,... "Doyle, Arthur": {"categories": {"mystery": {"titles": ["The Case of the Spotted Dick","Pre Emptive Needs Based Approach","The Case of the Mashed Potato"]},"thriller": {"titles": ["The Case of the Mashed Potato","The Pound Puppies of the Baskervilles"]}}},...

試算表 (Spreadsheets)

Sometimes data comes from spreadsheets rather than relational databases. Treeize is adept at handling this case, too. Instead of using descriptive keys as we did with rowset data in JSON format, the same descriptive format is used as column values in a header row:

有時數據來自電子表格，而不是關系數據庫。 Treeize也擅長處理這種情況。 與使用JSON格式的行集數據一樣，不使用描述性鍵，而是將相同的描述性格式用作標題行中的列值：

var seed = [ ['name', 'categories:type', 'categories:titles:name'], ['Doyle, Arthur', 'mystery', 'The Adventure of the Gyring Gerbils'], ['Schuppe, Katarina', 'engineering', 'Configurable Discrete Locks'], ['Doyle, Arthur', 'mystery', 'Holmes Alone 2'], ['Asimov, Isaac', 'science fiction', 'A Crack in the Foundation'] ];// same as before... var authors = new Treeize(); authors.grow(seed); return authors.getData();

There are quite a few options that treeize supports, and I’ve only shown the basics. It is a powerful tool that makes light work of transforming row-based data structures.

treeize支持的選項有很多，而我僅展示了基礎知識。 它是一個強大的工具，可以輕松地轉換基于行的數據結構。

Complete source can be found at my GitHub.

完整的源代碼可以在我的GitHub上找到 。

翻譯自: https://www.freecodecamp.org/news/spreadsheets-and-rowsets-getting-you-down-fd6ff7599052/

json數據轉換成表格

總結

以上是生活随笔為你收集整理的json数据转换成表格_电子表格会让您失望吗？ 将行数据转换为JSON树很容易。的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。