3

Many DBMSs started allowing JSON in a database. Does this violate 1NF?

One of the rules of 1NF is not storing multiple values in one column. But we are storing JSON, which can contain a key-value pair. Does this break the rule?

philipxy
  • 14,867
  • 6
  • 39
  • 83
DRSK FUN cuts
  • 69
  • 1
  • 5
  • Does this answer your question? [Normalization in database management system](https://stackoverflow.com/questions/40623169/normalization-in-database-management-system) – philipxy Oct 22 '22 at 18:33

1 Answers1

7

If you think of a single JSON column as just an atomic attribute column, then it's fine with respect to normal forms.

The problem comes when people try to treat the fields within the JSON document as if they are attribute columns. Searching, sorting, joining, aggregating, etc. against JSON fields as if they are discrete attributes.

This is even worse than violating 1NF. It violates the fundamental definition of a relation. So it can't even satisfy the preconditions of 1NF.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 2
    Very well said, certainly much better than the comment I was in the process of writing. I was on a project that stored XML in a column; the XML contained data the app needed to query upon. The rationale for this arrangement was it simplified the database because any new fields could be added to the XML without modifying the schema. Getting rid of the XML was one of the first things I did when I joined that project...it was absolutely horrible to work with. – Paul Oct 22 '22 at 16:58
  • 1
    @Paul, Yes! XML, YAML, serialized PHP objects, or any other format for semi-structured data suffers the same problem. Congrats on plugging that leak. – Bill Karwin Oct 22 '22 at 17:09
  • 1
    @Paul Fighting the good fight against XML! – tadman Oct 22 '22 at 17:09
  • The one of the rule of 1NF is not storing multiple values in one column. But we are storing json which can contains more key value pair. Does this not violate 1NF? – DRSK FUN cuts Oct 22 '22 at 17:36
  • 1
    But if you think of the JSON as simply a string which happens to contain commas and quotes and stuff like that, then it's a single value. That's what I mean by a single attribute in my answer above. – Bill Karwin Oct 22 '22 at 18:28