JSON Tables in BitRewriter

The JSON Table in the BitRewriter platform is a core concept to how we model the manipulation and visualization of JSON data.
It offers a structured and intuitive way to interact with hierarchical data that is based on common tabular structures and conventions.

1. Key Concepts

   • Distinct Tables for Each Object Type.

     Each unique object type in your JSON data is represented by its own table.

     An object type is defined by the chain of properties (JSONPath) leading from the root object.

     In this context, the position of an object within any array is not relevant, all such objects are considered to be of the same 'type'.

     This ensures that objects with the same structure & depth are represented by rows in the same table, regardless of the location of the object instances throughout the JSON data.

     
     {
       "bookstoreId": 12345,
       "bookstoreCatalog": {
         "items": [
           {
             "category": "reference",
             "author": "Cormac Reeser",
             "title": "Things About Things",
             "price": 8.95,
             "shelfDetails": [
               {
                 "shelf": "Front3",
                 "quantity": 10
               },
               {
                 "shelf": "Back7",
                 "quantity": 100
               }
             ]
           },
           {
             "category": "fiction",
             "author": "Minny Valdo",
             "title": "Space Politics in the 10th Century",
             "price": 12.99,
             "shelfDetails": [
               {
                 "shelf": "Back8",
                 "quantity": 3
               }
             ]
           }
         ]
       }
     }
     
     

     ⇛

     Table: Root

     [Id]	bookstoreId
     0	12345

     Table: bookstoreCatalog

     [Id]	[Parent.Id]
     0	0

     Table: items

     [Id]	[Parent.Id]
     0	0
     1	0

     Table: items[*]

     [Id]	[Parent.Id]	author	category	price	title
     0	0	Cormac Reeser	reference	8.95	Things About Things
     1	1	Minny Valdo	fiction	13	Space Politics in the 10th Century

     Table: shelfDetails

     [Id]	[Parent.Id]
     0	0
     1	0
     2	1

     Table: shelfDetails[*]

     [Id]	[Parent.Id]	quantity	shelf
     0	0	10	Front3
     1	1	100	Back7
     2	2	3	Back8

   • Consistent Structure.

     All objects in any one table share the same depth, value properties & structure, as well as the chain of parent properties back to the root object.

     This consistency ensures that the data is organized, consistent, and easy to navigate, even for nested JSON structures.

   • Rows and Columns.

     Each instance of an object corresponds to a single row in its respective table.

     Each property name & value of the object instance corresponds to a table column/cell value.

     Any nested structures within an object go into their own table, like every other, linked to this parent object (see below).

   • Implicit Links to Parent Objects.

     All table rows have an implicit Id property, that is simply a counter that uniquely identifies that row within the table.

     Objects that are nested within other objects have an implicit Parent.Id property to link to their parent object's row in the parent object's table.

     This linking mechanism preserves the hierarchical relationships within the JSON data, allowing you to trace the path from any object back to the root.

     The Id and Parent.Id properties are exposed in the JSON Explorer's Table View, and in the various tabular downloads (e.g. .xlsx)

     Table View selections will highlight the relationships between linked rows across tables, so you can easily visualize the hierarchy of objects.

   • Transformation Ready.

     This tabular representation of JSON data is fully compatible with our upcoming transformation features.

     Transformations will operate on your data, producing different tabular structures according to your needs.

     Reduce nesting, simplify structures, transform and validate individual values, and filter out unwanted data.

2. Benefits

   • Enhanced Data Visualization.

     The JSON Table feature transforms complex JSON data into a familiar tabular format, making it easier to understand and work with.

   • Efficient Data Manipulation.

     By organizing data into tables, you can quickly filter, sort, and edit JSON data.

     This is particularly useful for complex heavily nested datasets where instances of the same type may not be close together at the textual level.

   • Preserved Hierarchy.

     The implicit linking between parent and child objects ensures that the original JSON hierarchy is maintained, providing a clear view of how data is nested and related.

3. Use Cases

   • Data Analysis.

     Easily analyze JSON data by viewing it in a structured table format. This is ideal for identifying patterns, trends, and anomalies.

     Transform your data into a variety of tabular formats to suit your needs. e.g. for use in Excel ™ or Sheets™

   • Data Transformation.

     Simplify the process of transforming JSON data by manipulating it in a tabular format. This is useful for data cleaning, enrichment, and integration tasks.

     More tools to help with this task are coming soon.

   • Debugging and Validation.

     Quickly validate the structure and content of JSON data by visualizing it together in tables, along with an analysis. This helps in identifying and fixing issues in the data.