1

Many people are excited about storing json data inside mysql table.
So I created a column type json and tried.

$string = '["BERN", "BERLIN", "AMSTERDAM"]';
$sql = "update posts set tags = :atags where id = :aid";
$st = $db->prepare($sql);
$st->execute(array(
    ":atags" => $str,
    ":aid" => $_POST['id']
));

stored value is - ["BERN", "BERLIN", "AMSTERDAM"]

another way:

$arr = json_decode($str);
$st->execute(array(
    ":atags" => $arr,
    ":aid" => $_POST['id']
));

stored value is again - ["BERN", "BERLIN", "AMSTERDAM"]

So this is my first question - is it ok that stored value is the same in both cases?

Second - what is the advantage of storing that value inside json comparing to varchar field?

I can search this varchar value as usual: 

select * from posts where tags like '%BERLIN%'...

Could someone give me an example - what can I do with json data - and cannot do with string data inside mysql table.

Thanks.

  • The two queries being the same is just whatever DB wrapper you're using (PDO?) being helpful and JSON-encoding the array for you. – IMSoP May 25 '18 at 11:10
  • The advantage of JSON is in being able to use JSON. From a storage point of view, there is at least some overhead to storing data as JSON, so you'd only do it if you were planning to work with JSON, and not jus plain text. – Tim Biegeleisen May 25 '18 at 11:12
  • @IMSoP, posts on your link are more then one year old. SQL column now have a specific `json` column type and many new `sql-json` functions now exists. This can be crucial for choosing `json` approach or not. It is too easy just to say - this question is duplicate! –  May 25 '18 at 11:53
  • @puerto The fact that the *answers* are out of date doesn't stop the *question* being a duplicate, it just means those answers need updating, or a new one posting. The platform's not great at handling out of date content, but given that page likely has lots of links to it already ("viewed 14,104 times" according to the sidebar), creating a new page which nobody will find doesn't really help. Besides, JSON functions have been around in some form for years (e.g. PostgreSQL 9.2, released September 2012; MySQL 5.7, released October 2015) so could easily have been reflected in answers last year. – IMSoP May 25 '18 at 12:18
  • There might be *other* reasons why this is a different question - if so, you could [edit] your description to explain why the answers you're hoping for here would never belong on that other page. – IMSoP May 25 '18 at 12:19
  • The question does not seem to be a duplicate... the other is about *storing* JSON, which is not really the point of native JSON support. This question -- despite the fact that the examples do not address them -- seems to be about the capabilities offered by the `JSON` data type and related JSON manipulation functions, which OP does not understand, thus the question. – Michael - sqlbot May 29 '18 at 22:44

0 Answers0