JSON Support
Go Up to Supported Database Platforms
Contents
The Javascript Object Notation (JSON) is a file-based format containing a hierarchical structure of fields. In ER/Studio, these hierarchical structures are modeled, in physical models, using tress of Nested Objects linked via special containment relationships. ER/Studio allows you to model these hierarchical structures and convert them to and from relational logical data models containing standard entities connected via relationships with the containment flag set. A common use case is to create a logical data model representing a canonical or polyglot model that represents the structure of the information. From this multiple physical models can be created for any target data platform including JSON. Thus a common logical model can drive the designs for multiple JSON message structures held as physical data models, ensuring the information is consistent across them as we do for database platforms.
Data Architect reverse engineers JSON instance data or JSON Schema files to produce a physical data model. For the hierarchical structures in these JSON documents, Data Architect renders these as rolled-up structures inside the Collections for ease of viewing. ERDA also generates a logical data model based on the objects it finds within the JSON.
Complex JSON Columns in Traditional Databases
Beginning with version 20.9, ER/Studio Data Architect supports nested JSON objects for the following platforms:
- Azure SQL DB
- Azure Synapse Analytics
- Microsoft SQL Server 2019, 2022
- Oracle 19c, 23ai
- PostgreSQL
- Snowflake
This allows you to create hybrid physical models that are part traditional relational database, and part embedded JSON. In these platforms you can type columns as a flexible or complex datatype such as JSON, VARIANT, or NVARCHAR that may be used to store JSON data. In previous releases you may have designed these columns in your database but post implementation you would have no visibility on the content of the columns. Now when you reverse engineer these platforms you have an option in the Reverse Engineer wizard to parse the data in columns of such datatypes and look for JSON data. You can specify which datatypes and how many records to examine. If Data Architect finds valid JSON it will create structures of Nested Objects in your physical model documenting their content. In turn it will also create entities in your logical model so that both the embedded JSON and the first order tables and columns are described. This aids visibility and your Data Governance initiative.
Likewise, when designing a physical model from a logical model you can decide on how that information is deployed with more flexibility. Using containment flags in your logical model you can decide whether entities are deployed as first order tables and columns or as embedded JSON. When you generate the code for your database you can then generate both the DDL for the tables and columns and also JSON instance or schema files for the embedded JSON structure. Your developers then have all they need to realize these structures faithfully to your design and ensure that the data assets is well-documented and governable.
Generating a Physical Data Model for JSON
The Generate Database wizard differs for each target platform but for JSON models, specific options appear on Page 2 of the wizard.
Propagated Foreign Key properties can be removed from generated objects or made as native fields which may be useful later to connect objects together elsewhere via code.
Generating a Physical Model from a Logical Model
You can specify how you want Tables created from Logical Entities in the Generate Physical Model and Compare and Merge Utility wizards. Select from the following options:
- Create All as Tables. Use this option to create all entities as regular tables and ignore containment.
- Create as Tables unless Contained. When selected, this option converts any relationship marked as "containing" in the logical model into a containment relationship, and the contained entities are generated as Nested Objects.
Based on the supported database platform, you can define what data appears in your standard Tables and Columns while also generating SQL code as well as JSON Schemas and sample instance files.
In addition to the table structure, ERDA now reads in data, one record (row) at a time, including all columns of the specified data types, and excluding the rest. All relevant columns are combined into one JSON structure for efficiency. This JSON structure is then parsed, and Nested objects are created based on the contents.
For SQL Server, we have a size limit of 1MB for data of all relevant columns from one record. If data from a single record exceeds this limit, it is truncated to 1MB. Any JSON structures which are fully read in are used to generate Nested Objects. Those JSON structures that are truncated, or are not read in at all, as they occur in later columns, are not generated. You may receive the following warning message:
Table: <table_name>, Record: <row#> - Some of the JSON data may be truncated because it exceeds the maximum length.
For columns that are marked as valid JSON data types, but do not have any JSON structure data in them, you may receive the following warning message:
No valid JSON found in sample for <table_name>.<column_name>
The following fields are available in the Reverse Engineer and Compare and Merge Utility wizards for this feature:
- variant, object, and array. Select the appropriate columns of data types that may store JSON.
- Number of records to parse. Select how many records you want to parse. You may want a large number as you may have some records that contain JSON with incomplete sets of fields and parsing additional records can provide a complete set.
Note: Select Combine into single nested object as you may be interested in common objects across those JSON structures and want them combined into a simple set.
For more information about this feature, see Reverse engineer embedded JSON structures
The Rolled Up View
The object view is useful to understand the reuse of objects and edit the cardinality to Nested Objects. You can also select a rendering of the model that is easier to visualize the hierarchical nature of the model.
- On the Diagram tab of the ribbon bar, click Diagram/Object Display
- On the Object tab, check the Roll Up Contained Objects box.
- Another version of the rolled up view:
Import File
For a walkthrough of how to import a JSON instance or schema file, please see Importing a Model from a JSON File.
Nested Objects and Containment Relationships
Note: You can create containment relationships only between Table and Nested Object, and between Nested Object and Nested Object. Non-containment relationships are not allowed.
Nested Objects
Where objects are nested within objects, the system creates separate objects that are created with a containment relationship. For example the following JSON object has nested objects within the "person" object:
"person": {
"name": {
"first": "John",
"last": "Smith"
},
},
Tip: Separate object creation is useful for improved modeling. For example, nested objects such as address can be shared between different objects rather than duplicated. This also allows modifications to be made to all address fields without having to change each nested item individually.
Click Insert > Objects > Nested Object.
For JSON embedded in traditional databases you will see slightly different datatype names than for nested objects in JSON physical models:
- JSON_BOOLEAN
- JSON_INTEGER
- JSON_NUMBER
- JSON_OBJECT
- JSON_STRING
If you open older version files in this version, you must follow a few steps to enable the new JSON datatypes in the existing models. To enable new JSON datatypes in existing models, click Model > Model options > Update Datatype Mapping. Once you click, the system updates the existing mappings and applies the new data types.
Containment Relationships
Click Insert > Relationships > Containment Relationship.
Users also can switch the Object Class and Relationship Class for existing relationships. When viewing the item you want to switch, click Insert > Switch Object Class or click Insert > Switch Relationship Class. For more information about switching relationship classes, see Changing a relationship type for containment relationships.
JSON Schema Properties
The Table Editor allows you to view and edit the properties of an object and its fields. Many of these properties won't apply to JSON instance files but will be used in JSON Schema generation.
Features such as enumerated lists can be created by using Reference Values and binding them to fields in the Reference Values tab. For more information see Defining Valid Attribute Data Using Reference Values