3

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"]
      }
    }
  }
}
Improve this question
8
  • 4
    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 Sep 12 '20 at 19:03
  • You certainly CAN make a json to tables converter that will, given a finite sized chunk of json turn it into a finite number of tables and rows, according to some conventions you define. That's really not a problem. The problem is making those tables and rows make sense to anyone. Google search 'normalize json' and 'flatten json'. There's a lot of work out there, some of it in open source form. – joshp Sep 12 '20 at 19:06
  • 7
    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 Sep 12 '20 at 19:15
  • 1
    NFs apply to relations, so what does your question even mean? – philipxy Sep 13 '20 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 Sep 13 '20 at 8:22
6

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.

Improve this answer
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. – Jörg W Mittag Sep 12 '20 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 Sep 12 '20 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! – James Madison Jan 12 at 9:18
  • @JamesMadison Thank you for this feedback! – Christophe Jan 12 at 10:33
  • @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! – James Madison Jan 12 at 11:06
3

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.

Improve this answer
1
  • 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 Sep 13 '20 at 8:14
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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