What is the normal form of JSON?

Question

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 apprecaited.

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 fashing 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.

Thanks!

{
  "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"]
      }
    }
  }
}

Answer 1

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.

Answer 2

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.

Answer 3

This is actually a tricky question, since normalization and normal forms is defined in terms of relations and tuples (i.e. tables with typed columns). So you can't really talk about the normal form of tree-structures data like the Json example.

The data have to be in table form before you can meaningfully talk about normal forms. The JSON itself cannot be said to have any normal form.

If you put the JSON into table form, you get:

 id    | name | age | address     | favorite colors
 --------------------------------------------------
 cust1 | Jane | 33  | Main Street | blue, green
 cust2 | Joe  | 44  | West Road   | red, yellow

The "favorite" column breaks first normal form by having multiple values. So the table is not even in first normal form. This is sometimes called zeroth-normal form or 0NF.

You question is if a translation from JSON into 0NF table form can be done automatically or requires domain knowledge. I'll say it can be done automatically in a number of ways. Any arbitrary JSON structure can be represented as tables. It is just that the resulting tables will be 0NF and therefore subject to all the problems of denormalized data. So it is not something I would recommend.

An example could be a table of the form:

node id | name | type   | value | parent node id
------------------------------------------------
     1  | data  | object |      | NULL
     2  | cust1 | object |      | 1
     3  | name  | string | Jane | 2

And so forth. This would be able to represent any JSON payload, but would also be extremely tedious to query.