4

This is going to sound like a trivial question, but I like to think it's actually a deep one. The simple quesiton is, "What is the normal form of a typical JSON object?" For reference, I put an example below, but consider any typcial JSON object you've dealt with, same question applies.

I ask this theoretical question for a practical reason. In practice, we often need to convert JSON objects to some set of tables. Once they are tables, the tables have measurable normal forms based on all the usual rules of normal forms.

But getting to those tables with their normal form takes work. Now, what else "takes work". Answer: going from lower normal forms to higher normal forms. What doesn't "take work", is going down the normal forms. Or at least just a trivial amount of work. That is, if I have 6NF, I can rather quickly manipulate my way down to any lower normal form. If I have, say 2NF, and I need to work my way to at least 5NF for some practical reason, I have much work to do.

Well...since it is rather hard to get JSON to any decent normal form, then intuitively it seems it must be in a very low normal form. I'm hoping someone here can quantify that normal form of the JSON. Much appreciated.

But I still haven't given the most critical rationale. It is not uncommon for non-technical leaders to ask for miracles. I'm not criticizing, we all know it happens. And the miracle is something of the form, "just write some code to automatically make JSON into tables".

But wait! If my theory is correct, and JSON is basically 0NF or so, then you can't automate you way out of it. You can't go from the very low NF of JSON to anything decent, such as 3NF+, in an automated fashion because that "takes work". That is, it takes smart humans understanding the domain.

Now, I know some trivial JSON can become some trivial tables. I know there are a few tools that handle the simple cases. But I believe a general purpose JSON-to-Table converter is theoretically not possible because JSON is so low on the normalization information (in the rigorous Claude Shannon sense), that you can't automate it away.

So, what is the normal form of a typical JSON object? And is there some theory I didn't find that already proves you can't automate your way out of this.

{
  "data": {
    "cust1": {
      "name": "Jane",
      "age": 33,
      "address": "Main Street",
      "favorites": {
        "colors": ["blue", "green"]
      }
    },
    "cust2": {
      "name": "Joe",
      "age": 44,
      "address": "West Road",
      "favorites": {
        "colors": ["red", "yellow"]
      }
    }
  }
}
8
  • 7
    Mu. JSON documents don't use a relational data model, so concepts like “normal form” don't apply. If anything, I'd argue that typical JSON documents are very normalized. I think your automatic converter will run into practical problems before it comes to CS concepts, for example: Is this object key a column name or an ID? Is this array unordered like a table, or ordered like a tuple?
    – amon
    Commented Sep 12, 2020 at 19:03
  • 8
    Amon is right. To think of json in terms of relational normal forms you would have to first decide what parts of the json are the relations and which are the attributes, and which contained json objects are parts of the same relation. Once you do this, relational theory will apply just fine. Just because json is not relational does not mean that functional dependencies do not exist. Json alone does not provide enough information to know where the functional dependencies are. Json with a schema could do that. Plain json represents a universe of possible relations, dependencies, normal forms.
    – joshp
    Commented Sep 12, 2020 at 19:15
  • 2
    NFs apply to relations, so what does your question even mean?
    – philipxy
    Commented Sep 13, 2020 at 5:47
  • 4
    This is really an excellent question to make us think about the representation of data (syntax) and its semantic (is the data a relation or not) and the transposition of semantic properties (normal form) to the representation. I really thought I would never read Codd’s paper again 30 years after my first reading, but yet it could still stimulate the reflexion in a new perspective, despite written 50 years ago!
    – Christophe
    Commented Sep 13, 2020 at 8:22
  • 1
    @Christophe It's on an interesting topic in that it's asking for a (needless) precis on normalization & the relational model but the actual (unresearched) question asked doesn't make sense. Re your "but", the question is clearly addressing not that case. And you led with "yes".
    – philipxy
    Commented Sep 13, 2020 at 8:55

5 Answers 5

12

In short

JSON is a data representation according to a schema-less syntax without predefined semantics. On the opposite, normal forms are defined for abstract data model with a relational semantic according to a fixed schema. Therefore, it does not make sense to apply normal forms to JSON.

You can however add a schema or some semantics to your JSON format that would allow normal form analysis. But despite the feasibility, it is generally of little benefit, because a rich object model with nested and related objects are meant to expresses self-contained data differently and more flexibly than through fixed predefined tabular relations.

More details

Does it make sense?

The normal form was invented in the context of relational models by the pioneer Edgar F. Codd. The theory of the relational algebra is not about tables and columns, but about abstract relations, attributes, and sets (that can easily be represented with tables). The normal form is about the data (tuples) in the relations, the form of their atributes, and their interdependencies.

JSON is not a model but a representation of data with a precise syntax but without defined semantic. There is no rule about how to relate two different objects: Every JSON represents a different object and could represent a unique relation, made of a single tuple and not related to any others, or represent a set of related instances of a relation.

Conclusion: The concept of normal form does not apply to JSON objects, because it's defined for a relational model and JSON is used in radically different models (typically the document model).

Could it make sense?

Nothing prevents you to add some semantic to the JSON syntax. It is not rare that a set of JSON documents are related and represent tuples of the same relation, and that elements that share a same name correspond to the same attribute and have their potential values in the same domain (following an implicit or explicit schema). In fact your example uses JSON exactly this way.

At what level should the normal form be considered?

  • Do you consider the JSON object itself as a single attribute in a relation? Since it is not elementary/atomic but made of an aggregation of several elements, it would be indeed UNF.
  • Do you consider the JSON as a tuple? After all, Codd noted tuples (a,b,c) using the order of the attribute names (p1,p2, p3) and did never pretend a tuple was UNF. So {p1:a, p2:b, p3:c} could easily be considered 1NF if each of its elementary/atomic.

In the second case, there are however some more questions. What if:

  • some elements are nested objects: these are not atomic. So do we consider them as a separate relation and apply the rule about normal form recursively, looking within the embedded JSON? Or do we conclude that any JSON containing an embedded JSON is no longer in 1NF?
  • some elements are arrays: these are not atomic either. So do you consider that it's just not normal form, or do you consider the array as a relation defined by enclosed tuples and you then look recursively at each array element?

Conclusion: Adopting some semantics to the JSON syntax allows to apply normal form analysis.

How to extend normal form to JSON?

In practice, with the semantic defined in the previous section, and choosing the recursive analysis for the open questions, you define a mapping between you JSONs and a relational form. In fact, a researcher team at Yale even published a paper to describe such an algorithm.

With such a mapping you may just apply the normal-form critera to the mapped relational model to categorize your JSON representation.

For example this JSON:

{ customers: [ { id:1, name:"Smith", turnover:324233.22}, 
               { id:2, name:"Wesson", turnover:1600256.00} ], 
  products:  [ { id:1234, label:"Screwdriver", lauched: { y:2019,m:9 }}, 
               { id:1235, label:"Hammer (row)", lauched: { y:2011,m:1 }} ]
}

could have the following relational mapping:

TABLE CUSTOMERS (id, name, turnover); 
TABLE PRODUCTS (id, label);
TABLE PRODUCT-LAUNCH (product-id, year, month);  

So you could claim the JSON is BCNF, because the relational mapping has tables with only atomic attributes, that the attributes of each table solely depend on the primary key and not a part of primary key, that obviously there is no transitive dependency, ...

But what's the benefit?

I claim that normal form for JSON does in most case not have any benefit:

  • If you chose a JSON encoding and a NOSQL document database, it's because you want to free yourself of the relational model. Not because the relational model would be bad (in fact it is excellent and achieved outstanding performance in domains where it fits the needs), but because the relational model probably doesn't fit your specific needs. It makes then no sense to introduce artificial constraints.

  • If your whole design is based on rich business objects and you do not want to flatten and rehydrate them via an ORM layer, the normal form will not help you: your objects are self-contained and redundancy may not matter in the same way it does in tables. This is exactly why it is usually analysed case-by-case hot to implementing one-to-many associations in a document database, i.e. embedded documents vs. references to other documents.

Conclusion: The normal form does in general not add benefits to JSON, unless you need to do ORM. However, the thoughts about redundancies and functional dependencies, which are core ingredients of the normal forms, may help to assess the boundaries between objects.

5
  • "But some are themselves JSONs, so not elementary." – Yikes, do people really do that? I mean, it is obviously possible since JSON has strings and JSON documents are strings, so you can put a JSON document into a string, but who would do something like that? I know that there are formats where people put other textual formats into strings into JSON documents (e.g. SDPs) but I've yet to see recursive JSON, thankfully. That sounds like a nightmare. Commented Sep 12, 2020 at 20:50
  • @JörgWMittag Maybe I was ambiguous, but sure they do. Here a couple of examples with embedded arrays: docs.mongodb.com/manual/tutorial/… and here without arrays: stackoverflow.com/a/2098294/3723423
    – Christophe
    Commented Sep 12, 2020 at 21:35
  • Outstanding analysis. I don't agree with the conclusion that "normal form...does not add benefits to JSON". My context is hundreds of people in a data science role that need "tidy data" (vita.had.co.nz/papers/tidy-data.pdf), which is basically BCNF. We get hundreds of data sets in JSON and have hundreds of customers who want it tidy, so it's a massive use case! But love the detailed analysis. Even as I write this, someone in the organization has claimed to have written yet another general purpose JSON normalizer...can't wait to see! Commented Jan 12, 2021 at 9:18
  • @JamesMadison Thank you for this feedback!
    – Christophe
    Commented Jan 12, 2021 at 10:33
  • 1
    @Christophe And as I look at my question, there is no way for the reader to know that my context is data science feeds versus applications or a similar ORM context, so my fault on that. I won't revise the question at this point, and I can't change my comment above. But you're right, in an application/ORM, JSON rocks. But my use case is feeds to a large data science community needing tidy data, which is a totally different lense! Commented Jan 12, 2021 at 11:06
5

Zeroth.

First Normal Form says that data should be atomic. As in a single boolean, a single number. Even a single string is already questionable. It depends on how it is used, a string could be used to represent something, in which case it is not really atomic data anymore. In fact, even a number could be used this way.

So, in general, a JSON document is in Zeroth Normal Form because it is, well, a document, not a single atomic value.

It is possible to have a JSON document in First Normal Form, for example this document:

true

However, even this document is already no longer in First Normal Form:

{ "property": true }

It is not an atomic data value, it is an object containing a key value pair where the key is a string and the value is a boolean.

Of course, in actual fact, the definition of First Normal Form talks explicitly about Relations (or Tables), and so the real answer is: JSON doesn't have Relations or Tables, so the very question is non-sensical.

2
  • 1
    If Codd defined a normal form for a set of tuples representing a relation, and noted the tuples (a,b,c) and the sets { (a,b,c), (d,e,f)}, wouldn’t your reasoning conclude that no relational model has a normal form? In other words, isn’t there a confusion between the representation syntax and the model represented?
    – Christophe
    Commented Sep 13, 2020 at 8:14
  • "Atomic" is in regards to the relational operators - projection, filter etc. It is not about the semantics or use of the value. While a string is composed of characters you can't select the individual characters without using special-purpose operators. So strings are atomic in regards to the relational operators - at least in any relational database implementation I know of.
    – JacquesB
    Commented May 4 at 15:33
1

This was a great conversation.

For relational -> JSON, famous quip aside, you CAN "get there from here" - you just can't get back - due to the information that's lost because JSON has no way to represent it.

This is not totally different than what happens when creating a dimensional model for a data warehouse. Relationships between table pairs that were codified as foreign key constraints become unenforced relationships between column subsets. They can't be enforced using recursive foreign keys because even apex reference tables' data are no longer unique when placed in a type II slowly changing dimension, the most common type of dimension.

Oracle Database has, however, addressed this with "dimension objects", within which you group a dimension table's columns into one or more "hierarchies". Each hierarchy is composed of one or more "levels", with each level being composed of all columns at the same grain. This often means columns from the same source table.

I have just finished creating a factless dimensional model for a data warehouse that will be used to build JSON documents on the fly. Each JSON document will be cast as a PDF for access by users. These documents will clearly have parent-child data subsets. Fortunately, we can "get there from here" - and we don't have to get back.

1

A hierarchical data structure does not conform to first normal form. Such data is usually just called unnormalized. (Sometime it is called 0th normal form, but this is a term without a precise definition.)

You can normalize hierarchical data to first normal form mechanically as long as every record have a unique key. The process is to extract nested structures (like the colors array) and then attach the primary key of the containing structure. If you have multiple levels of nesting you just repeat the process.

The normalized form of your example will then look like this:

  | cust ID | name | age | address     |
  |---------|------|-----|-------------|
  | cust1   | Jane |  33 | Main Street |
  | cust2   | Joe  |  44 | West Road   |

  
  | cust ID | color  | position |
  |---------|--------|----------|
  | cust1   | blue   | 1
  | cust1   | green  | 2
  | cust2   | red    | 1
  | cust2   | yellow | 2

The result of this normalization also happen to comply to higher normal forms, which is often the case.

In the example I assume the object keys in the JSON structure are unique. This is strictly speaking not guaranteed by JSON even though it a common convention. In case the keys are not unique, you need some information which is not given by the data itself to determine which fields to use as primary keys.

(Note I added the array position as a number. I assume the order may carry information, and since relations are unordered, this information have to be an explicit value in the data.)

You are correct that you cant go from 1st normal form to higher normal forms without an understanding of the semantics of the data - in particular the so-called functional dependencies. But following the above process you will not introduce redundancies unless they already exist in the hierarchical data.

-3

JSON has a tree structure. It is not a database. You can include a database table by adding an array of dictionaries with the same keys if you like. But you can also add multiple database tables that way, or have an array or dictionary of database table. And then some more data that is not part of any database.

Important is that you can't have any expectation that you can edit a JSON document other than by loading it into memory, making changes, and writing back to permanent storage,

Not the answer you're looking for? Browse other questions tagged or ask your own question.