Violation of 1NF in MySQL

Question

MySQL supports JSON data type. Is it violating 1NF rule for atomicity of fields? If no then how is it justified?

Answer 1

It depends on how the data is used within the rdbms, not just on the data type itself, whether any data (not just json) violates the 1NF. However, data type influences how often a data type is used to store data in a way that violates 1NF. Json being a complex data type with intrinsic structure, is very likely to be used in a way that violates 1NF.

Let me expand a bit what I mean by the use of the data as decisive factor in this question.

The wikipedia article on 1NF has a section on atomicity that describes the ambiguity around the interpretation of atomicity:

the notion of atomicity has no absolute meaning":[10] a value may be considered atomic for some purposes, but may be considered an assemblage of more basic elements for other purposes.

This means that if the particular json field is handled in an atomic way oblivious of its internal structure in the database layer, then it will not violate the 1NF.

However, if the database must be aware of the internal structure of a particular json field (e.g. search within it), then it violates 1NF.

To offer an analogue, strings stored in a varchar field would be considered atomic by most users (apart from zelous theorists). But if you store delimited values (or json or xml for that matter) and your database layer must be aware of such field's internal structure, then even a varchar field may violate the 1NF.