72

I am planning to store a json_encoded string on my database. I can't precisely tell the length its going to be, but I'm pretty sure it will be long. My concern is which field type I am going to use for this, is it blob or text?

I prefer the one where I can save space as much as possible over fast searching, in any case I have other column where I should just index.

dreftymac
  • 31,404
  • 26
  • 119
  • 182
Leandro Garcia
  • 3,138
  • 11
  • 32
  • 44

2 Answers2

68

As stated in the documentation of MySQL, since 5.7.8 a native JSON data type is supported.

The JSON data type provides these advantages over storing JSON-format strings in a string column:

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
  • Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

So, as the MySQL documentation states, the JSON data type should be used and not the text.

cs04iz1
  • 1,737
  • 1
  • 17
  • 30
  • 10
    _Optimized storage format._ Does that mean, smaller space needed? – luckydonald Jul 18 '18 at 17:19
  • 12
    Retrieved from this link: https://dev.mysql.com/doc/refman/5.7/en/json.html `The space required to store a JSON document is roughly the same as for LONGBLOB or LONGTEXT;` – Natan Jan 22 '19 at 13:49
  • 1
    From my experience, using JSON data type messed up the order of the keys that gets stored. For example, before encoding, my data was: `['a' => 1, 'b' => 2, 'c' => 3, 'd' => 4]`. After encoding and storing this data under the JSON data type, I found the data was stored as `{"c": "3", "a": "1", "b": "2", "d": "4"}`. I tried to use `JSON_FORCE_OBJECT` as an option for `json_encode` but that did not help. Do you know how we can preserve the order of the array elements/keys that are to be in the JSON data type? – Devner Feb 26 '20 at 09:01
  • 2
    @Devner What you're describing (dictionaries, or associative arrays, or hashmaps) are inherently unordered. You would need to store a mapping for keep one of them ordered. – Kerry Jones Sep 16 '20 at 20:23
  • @KerryJones Thanks! Do you happen to know how I can do that programmatically? – Devner Oct 02 '20 at 03:25
  • 2
    @Devner add an ordered array to your object, let's call it, `keysOrder`. It will contain your keys in the order you want. When you parse/decode your json, you will need to re-order the keys based on the `keysOrder` array. E.g: `{"c": "3", "a": "1", "b": "2", "d": "4", "keysOrder": ['a', 'b', 'c', 'd']}` – parse Jan 13 '21 at 23:56
  • @parse Thank you for your suggestion. I will try it out when I get a chance. – Devner Feb 14 '21 at 11:34
49

blob is usually for things like images, binaries etc. text should be good enough for your case, or you can use longtext which has even bigger space capacity if that's really a concern.

Searching-wise, since you are storing json_encode'd stuff, you'll still need to call json_decode on it anyway for it to be useful in your application, I don't think choice of datatype matters in this case.

A better way is to normalize your database design instead of storing related stuff in one big string of json.

Andreas Wong
  • 59,630
  • 19
  • 106
  • 123
  • 3
    I'm about 2 years and 11 months later, but as for payloads for webhooks, storing serialized JSON whilst waiting for it to be sent seems like a good use case for it. I've love feedback if someone thinks differently. – Mave Mar 12 '15 at 11:54
  • 2
    Pinterest is storing JSON in `text`, too, they [recently published it in this article (scroll down half way)](https://engineering.pinterest.com/blog/sharding-pinterest-how-we-scaled-our-mysql-fleet). – Daniel W. Sep 15 '15 at 13:23
  • I think text is larger than longtext in mysql. json data type defaults to longtext which is no good for everyday json objects. – Gandalf May 05 '21 at 12:46