Bug #75771 JSON_VALID UDF returns 0 for valid JSON
Submitted: 4 Feb 2015 18:50 Modified: 5 Feb 2015 15:53
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON User-defined function ( UDF ) Severity:S2 (Serious)
Version:5.6.21, mysql-json-udfs-0.3.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: incorrect result, json, udf

[4 Feb 2015 18:50] Justin Swanhart
Description:
I tried to use JSON_EXTRACT on some test documents I have, but I was getting NULL result.  Suspecting that the UDF thought the JSON was not correct, I tried testing JSON_VALID on the document.  This returns 0.

I know the document is valid JSON, but used a JSON lint tool to verify that indeed it is valid JSON.

How to repeat:

select json_valid('{ "_id" : { "$oid" : "52cdef7c4bab8bd675297d8b" }, "name" : "AdventNet", "permalink" : "abc3", "crunchbase_url" : "http://www.crunchbase.com/company/adventnet", "homepage_url" : "http://adventnet.com", "blog_url" : "", "blog_feed_url" : "", "twitter_username" : "manageengine", "category_code" : "enterprise", "number_of_employees" : 600, "founded_year" : 1996, "deadpooled_year" : 2, "tag_list" : "", "alias_list" : "Zoho ManageEngine ", "email_address" : "pr@adventnet.com", "phone_number" : "925-924-9500", "description" : "Server Management Software", "created_at" : { "$date" : 1180121062000 }, "updated_at" : "Wed Oct 31 18:26:09 UTC 2012", "overview" : "<p>AdventNet is now <a href="/company/zoho-manageengine" title="Zoho ManageEngine" rel="nofollow">Zoho ManageEngine</a>.</p>

<p>Founded in 1996, AdventNet has served a diverse range of enterprise IT, networking and telecom customers.</p>

<p>AdventNet supplies server and network management software.</p>", "image" : { "available_sizes" : [ [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-150x150.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-250x250.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-450x450.png" ] ] }, "products" : [], "relationships" : [ { "is_past" : true, "title" : "CEO and Co-Founder", "person" : { "first_name" : "Sridhar", "last_name" : "Vembu", "permalink" : "sridhar-vembu" } }, { "is_past" : true, "title" : "VP of Business Dev", "person" : { "first_name" : "Neil", "last_name" : "Butani", "permalink" : "neil-butani" } }, { "is_past" : true, "title" : "Usabiliy Engineer", "person" : { "first_name" : "Bharath", "last_name" : "Balasubramanian", "permalink" : "bharath-balasibramanian" } }, { "is_past" : true, "title" : "Director of Engineering", "person" : { "first_name" : "Rajendran", "last_name" : "Dandapani", "permalink" : "rajendran-dandapani" } }, { "is_past" : true, "title" : "Market Analyst", "person" : { "first_name" : "Aravind", "last_name" : "Natarajan", "permalink" : "aravind-natarajan" } }, { "is_past" : true, "title" : "Director of Product Management", "person" : { "first_name" : "Hyther", "last_name" : "Nizam", "permalink" : "hyther-nizam" } }, { "is_past" : true, "title" : "Western Regional OEM Sales Manager", "person" : { "first_name" : "Ian", "last_name" : "Wenig", "permalink" : "ian-wenig" } } ], "competitions" : [], "providerships" : [ { "title" : "DHFH", "is_past" : true, "provider" : { "name" : "A Small Orange", "permalink" : "a-small-orange" } } ], "total_money_raised" : "$0", "funding_rounds" : [], "investments" : [], "acquisition" : null, "acquisitions" : [], "offices" : [ { "description" : "Headquarters", "address1" : "4900 Hopyard Rd.", "address2" : "Suite 310", "zip_code" : "94588", "city" : "Pleasanton", "state_code" : "CA", "country_code" : "USA", "latitude" : 37.692934, "longitude" : -121.904945 } ], "milestones" : [], "video_embeds" : [], "screenshots" : [ { "available_sizes" : [ [ [ 150, 94 ], "assets/images/resized/0004/3400/43400v1-max-150x150.png" ], [ [ 250, 156 ], "assets/images/resized/0004/3400/43400v1-max-250x250.png" ], [ [ 450, 282 ], "assets/images/resized/0004/3400/43400v1-max-450x450.png" ] ], "attribution" : null } ], "external_links" : [], "partners" : [] }');

Suggested fix:
Unknown
[4 Feb 2015 20:18] Sveta Smirnova
Thank you for the report.

But jsonlint returns parse error for me when I use copy-paste. Please attach example as a text file to avoid copying extra characters.
[4 Feb 2015 22:09] Justin Swanhart
You can get the data at the following link.  It is free to public to download, but there is no license information so I am unsure if I am allowed to redistribute it, and it is also too large to attach.

http://jsonstudio.com/wp-content/uploads/2014/02/companies.zip
[5 Feb 2015 4:34] Justin Swanhart
JSONlint.com says this is valid, but JSON_VALID does not:
{ "_id" : { "$oid" : "52cdef7c4bab8bd675297d8b" }, "name" : "AdventNet", "permalink" : "abc3", "crunchbase_url" : "http://www.crunchbase.com/company/adventnet", "homepage_url" : "http://adventnet.com", "blog_url" : "", "blog_feed_url" : "", "twitter_username" : "manageengine", "category_code" : "enterprise", "number_of_employees" : 600, "founded_year" : 1996, "deadpooled_year" : 2, "tag_list" : "", "alias_list" : "Zoho ManageEngine ", "email_address" : "pr@adventnet.com", "phone_number" : "925-924-9500", "description" : "Server Management Software", "created_at" : { "$date" : 1180121062000 }, "updated_at" : "Wed Oct 31 18:26:09 UTC 2012", "overview" : "<p>AdventNet is now <a href=\"/company/zoho-manageengine\" title=\"Zoho ManageEngine\" rel=\"nofollow\">Zoho ManageEngine</a>.</p>\n\n<p>Founded in 1996, AdventNet has served a diverse range of enterprise IT, networking and telecom customers.</p>\n\n<p>AdventNet supplies server and network management software.</p>", "image" : { "available_sizes" : [ [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-150x150.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-250x250.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-450x450.png" ] ] }, "products" : [], "relationships" : [ { "is_past" : true, "title" : "CEO and Co-Founder", "person" : { "first_name" : "Sridhar", "last_name" : "Vembu", "permalink" : "sridhar-vembu" } }, { "is_past" : true, "title" : "VP of Business Dev", "person" : { "first_name" : "Neil", "last_name" : "Butani", "permalink" : "neil-butani" } }, { "is_past" : true, "title" : "Usabiliy Engineer", "person" : { "first_name" : "Bharath", "last_name" : "Balasubramanian", "permalink" : "bharath-balasibramanian" } }, { "is_past" : true, "title" : "Director of Engineering", "person" : { "first_name" : "Rajendran", "last_name" : "Dandapani", "permalink" : "rajendran-dandapani" } }, { "is_past" : true, "title" : "Market Analyst", "person" : { "first_name" : "Aravind", "last_name" : "Natarajan", "permalink" : "aravind-natarajan" } }, { "is_past" : true, "title" : "Director of Product Management", "person" : { "first_name" : "Hyther", "last_name" : "Nizam", "permalink" : "hyther-nizam" } }, { "is_past" : true, "title" : "Western Regional OEM Sales Manager", "person" : { "first_name" : "Ian", "last_name" : "Wenig", "permalink" : "ian-wenig" } } ], "competitions" : [], "providerships" : [ { "title" : "DHFH", "is_past" : true, "provider" : { "name" : "A Small Orange", "permalink" : "a-small-orange" } } ], "total_money_raised" : "$0", "funding_rounds" : [], "investments" : [], "acquisition" : null, "acquisitions" : [], "offices" : [ { "description" : "Headquarters", "address1" : "4900 Hopyard Rd.", "address2" : "Suite 310", "zip_code" : "94588", "city" : "Pleasanton", "state_code" : "CA", "country_code" : "USA", "latitude" : 37.692934, "longitude" : -121.904945 } ], "milestones" : [], "video_embeds" : [], "screenshots" : [ { "available_sizes" : [ [ [ 150, 94 ], "assets/images/resized/0004/3400/43400v1-max-150x150.png" ], [ [ 250, 156 ], "assets/images/resized/0004/3400/43400v1-max-250x250.png" ], [ [ 450, 282 ], "assets/images/resized/0004/3400/43400v1-max-450x450.png" ] ], "attribution" : null } ], "external_links" : [], "partners" : [] }
[5 Feb 2015 4:59] Justin Swanhart
Okay, so I found the problem.  MySQL uses \ for escape characters and this breaks JSON_VALID when called with the example JSON.  I am not sure how to fix this for string literals except to call REPLACE() on the JSON and replace \ with \\.

For regular tables, simply load the JSON and specify an empty escape character:
mysql> load data infile '/home/justin/Downloads/tmp/companies.json' into table j2 fields escaped by '' (doc);
Query OK, 18801 rows affected (2.91 sec)
Records: 18801  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select json_valid(doc) from j2 where id = 2;
+-----------------+
| json_valid(doc) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

but the exact same document as a string literal:
select json_valid('{ "_id" : { "$oid" : "52cdef7c4bab8bd675297d8b" }, "name" : "AdventNet", "permalink" : "abc3", "crunchbase_url" : "http://www.crunchbase.com/company/adventnet", "homepage_url" : "http://adventnet.com", "blog_url" : "", "blog_feed_url" : "", "twitter_username" : "manageengine", "category_code" : "enterprise", "number_of_employees" : 600, "founded_year" : 1996, "deadpooled_year" : 2, "tag_list" : "", "alias_list" : "Zoho ManageEngine ", "email_address" : "pr@adventnet.com", "phone_number" : "925-924-9500", "description" : "Server Management Software", "created_at" : { "$date" : 1180121062000 }, "updated_at" : "Wed Oct 31 18:26:09 UTC 2012", "overview" : "<p>AdventNet is now <a href=\"/company/zoho-manageengine\" title=\"Zoho ManageEngine\" rel=\"nofollow\">Zoho ManageEngine</a>.</p>\n\n<p>Founded in 1996, AdventNet has served a diverse range of enterprise IT, networking and telecom customers.</p>\n\n<p>AdventNet supplies server and network management software.</p>", "image" : { "available_sizes" : [ [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-150x150.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-250x250.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-450x450.png" ] ] }, "products" : [], "relationships" : [ { "is_past" : true, "title" : "CEO and Co-Founder", "person" : { "first_name" : "Sridhar", "last_name" : "Vembu", "permalink" : "sridhar-vembu" } }, { "is_past" : true, "title" : "VP of Business Dev", "person" : { "first_name" : "Neil", "last_name" : "Butani", "permalink" : "neil-butani" } }, { "is_past" : true, "title" : "Usabiliy Engineer", "person" : { "first_name" : "Bharath", "last_name" : "Balasubramanian", "permalink" : "bharath-balasibramanian" } }, { "is_past" : true, "title" : "Director of Engineering", "person" : { "first_name" : "Rajendran", "last_name" : "Dandapani", "permalink" : "rajendran-dandapani" } }, { "is_past" : true, "title" : "Market Analyst", "person" : { "first_name" : "Aravind", "last_name" : "Natarajan", "permalink" : "aravind-natarajan" } }, { "is_past" : true, "title" : "Director of Product Management", "person" : { "first_name" : "Hyther", "last_name" : "Nizam", "permalink" : "hyther-nizam" } }, { "is_past" : true, "title" : "Western Regional OEM Sales Manager", "person" : { "first_name" : "Ian", "last_name" : "Wenig", "permalink" : "ian-wenig" } } ], "competitions" : [], "providerships" : [ { "title" : "DHFH", "is_past" : true, "provider" : { "name" : "A Small Orange", "permalink" : "a-small-orange" } } ], "total_money_raised" : "$0", "funding_rounds" : [], "investments" : [], "acquisition" : null, "acquisitions" : [], "offices" : [ { "description" : "Headquarters", "address1" : "4900 Hopyard Rd.", "address2" : "Suite 310", "zip_code" : "94588", "city" : "Pleasanton", "state_code" : "CA", "country_code" : "USA", "latitude" : 37.692934, "longitude" : -121.904945 } ], "milestones" : [], "video_embeds" : [], "screenshots" : [ { "available_sizes" : [ [ [ 150, 94 ], "assets/images/resized/0004/3400/43400v1-max-150x150.png" ], [ [ 250, 156 ], "assets/images/resized/0004/3400/43400v1-max-250x250.png" ], [ [ 450, 282 ], "assets/images/resized/0004/3400/43400v1-max-450x450.png" ] ], "attribution" : null } ], "external_links" : [], "partners" : [] }') "is_valid?";
+-----------+
| is_valid? |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)
[5 Feb 2015 9:01] MySQL Verification Team
Thank you for the feedback.
Confirmed that With mysql-json-udfs-0.3.3 and 5.7.6:

mysql> select json_valid('{ "_id" : { "$oid" : "52cdef7c4bab8bd675297d8b" }, "name" : "AdventNet", "permalink" : "abc3", "crunchbase_url" : "http://www.crunchbase.com/company/adventnet", "homepage_url" : "http://adventnet.com", "blog_url" : "", "blog_feed_url" : "", "twitter_username" : "manageengine", "category_code" : "enterprise", "number_of_employees" : 600, "founded_year" : 1996, "deadpooled_year" : 2, "tag_list" : "", "alias_list" : "Zoho ManageEngine ", "email_address" : "pr@adventnet.com", "phone_number" : "925-924-9500", "description" : "Server Management Software", "created_at" : { "$date" : 1180121062000 }, "updated_at" : "Wed Oct 31 18:26:09 UTC 2012", "overview" : "<p>AdventNet is now <a href=\"/company/zoho-manageengine\" title=\"Zoho ManageEngine\" rel=\"nofollow\">Zoho ManageEngine</a>.</p>\n\n<p>Founded in 1996, AdventNet has served a diverse range of enterprise IT, networking and telecom customers.</p>\n\n<p>AdventNet supplies server and network management software.</p>", "image" : { "available_sizes" : [ [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-150x150.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-250x250.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-450x450.png" ] ] }, "products" : [], "relationships" : [ { "is_past" : true, "title" : "CEO and Co-Founder", "person" : { "first_name" : "Sridhar", "last_name" : "Vembu", "permalink" : "sridhar-vembu" } }, { "is_past" : true, "title" : "VP of Business Dev", "person" : { "first_name" : "Neil", "last_name" : "Butani", "permalink" : "neil-butani" } }, { "is_past" : true, "title" : "Usabiliy Engineer", "person" : { "first_name" : "Bharath", "last_name" : "Balasubramanian", "permalink" : "bharath-balasibramanian" } }, { "is_past" : true, "title" : "Director of Engineering", "person" : { "first_name" : "Rajendran", "last_name" : "Dandapani", "permalink" : "rajendran-dandapani" } }, { "is_past" : true, "title" : "Market Analyst", "person" : { "first_name" : "Aravind", "last_name" : "Natarajan", "permalink" : "aravind-natarajan" } }, { "is_past" : true, "title" : "Director of Product Management", "person" : { "first_name" : "Hyther", "last_name" : "Nizam", "permalink" : "hyther-nizam" } }, { "is_past" : true, "title" : "Western Regional OEM Sales Manager", "person" : { "first_name" : "Ian", "last_name" : "Wenig", "permalink" : "ian-wenig" } } ], "competitions" : [], "providerships" : [ { "title" : "DHFH", "is_past" : true, "provider" : { "name" : "A Small Orange", "permalink" : "a-small-orange" } } ], "total_money_raised" : "$0", "funding_rounds" : [], "investments" : [], "acquisition" : null, "acquisitions" : [], "offices" : [ { "description" : "Headquarters", "address1" : "4900 Hopyard Rd.", "address2" : "Suite 310", "zip_code" : "94588", "city" : "Pleasanton", "state_code" : "CA", "country_code" : "USA", "latitude" : 37.692934, "longitude" : -121.904945 } ], "milestones" : [], "video_embeds" : [], "screenshots" : [ { "available_sizes" : [ [ [ 150, 94 ], "assets/images/resized/0004/3400/43400v1-max-150x150.png" ], [ [ 250, 156 ], "assets/images/resized/0004/3400/43400v1-max-250x250.png" ], [ [ 450, 282 ], "assets/images/resized/0004/3400/43400v1-max-450x450.png" ] ], "attribution" : null } ], "external_links" : [], "partners" : [] }') "is_valid?";
+-----------+
| is_valid? |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

Where as http://jsonlint.com/ confirms it as Valid JSON
[5 Feb 2015 15:53] Justin Swanhart
Umesh,

This should be "closed" because it isn't really a bug.  The problem is due to the backslashes in the JSON (like \n) but it is not MySQL's fault that it replaces \n with a real newline, this is expected MySQL behavior.  

Here is a working example of the SAME JSON where \ has been properly escaped as \\:
mysql> select json_valid('{ "_id" : { "$oid" : "52cdef7c4bab8bd675297d8b" }, "name" : "AdventNet", "permalink" : "abc3", "crunchbase_url" : "http://www.crunchbase.com/company/adventnet", "homepage_url" : "http://adventnet.com", "blog_url" : "", "blog_feed_url" : "", "twitter_username" : "manageengine", "category_code" : "enterprise", "number_of_employees" : 600, "founded_year" : 1996, "deadpooled_year" : 2, "tag_list" : "", "alias_list" : "Zoho ManageEngine ", "email_address" : "pr@adventnet.com", "phone_number" : "925-924-9500", "description" : "Server Management Software", "created_at" : { "$date" : 1180121062000 }, "updated_at" : "Wed Oct 31 18:26:09 UTC 2012", "overview" : "<p>AdventNet is now <a href=\\"/company/zoho-manageengine\\" title=\\"Zoho ManageEngine\\" rel=\\"nofollow\\">Zoho ManageEngine</a>.</p>\\n\\n<p>Founded in 1996, AdventNet has served a diverse range of enterprise IT, networking and telecom customers.</p>\\n\\n<p>AdventNet supplies server and network management software.</p>", "image" : { "available_sizes" : [ [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-150x150.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-250x250.png" ], [ [ 150, 55 ], "assets/images/resized/0001/9732/19732v1-max-450x450.png" ] ] }, "products" : [], "relationships" : [ { "is_past" : true, "title" : "CEO and Co-Founder", "person" : { "first_name" : "Sridhar", "last_name" : "Vembu", "permalink" : "sridhar-vembu" } }, { "is_past" : true, "title" : "VP of Business Dev", "person" : { "first_name" : "Neil", "last_name" : "Butani", "permalink" : "neil-butani" } }, { "is_past" : true, "title" : "Usabiliy Engineer", "person" : { "first_name" : "Bharath", "last_name" : "Balasubramanian", "permalink" : "bharath-balasibramanian" } }, { "is_past" : true, "title" : "Director of Engineering", "person" : { "first_name" : "Rajendran", "last_name" : "Dandapani", "permalink" : "rajendran-dandapani" } }, { "is_past" : true, "title" : "Market Analyst", "person" : { "first_name" : "Aravind", "last_name" : "Natarajan", "permalink" : "aravind-natarajan" } }, { "is_past" : true, "title" : "Director of Product Management", "person" : { "first_name" : "Hyther", "last_name" : "Nizam", "permalink" : "hyther-nizam" } }, { "is_past" : true, "title" : "Western Regional OEM Sales Manager", "person" : { "first_name" : "Ian", "last_name" : "Wenig", "permalink" : "ian-wenig" } } ], "competitions" : [], "providerships" : [ { "title" : "DHFH", "is_past" : true, "provider" : { "name" : "A Small Orange", "permalink" : "a-small-orange" } } ], "total_money_raised" : "$0", "funding_rounds" : [], "investments" : [], "acquisition" : null, "acquisitions" : [], "offices" : [ { "description" : "Headquarters", "address1" : "4900 Hopyard Rd.", "address2" : "Suite 310", "zip_code" : "94588", "city" : "Pleasanton", "state_code" : "CA", "country_code" : "USA", "latitude" : 37.692934, "longitude" : -121.904945 } ], "milestones" : [], "video_embeds" : [], "screenshots" : [ { "available_sizes" : [ [ [ 150, 94 ], "assets/images/resized/0004/3400/43400v1-max-150x150.png" ], [ [ 250, 156 ], "assets/images/resized/0004/3400/43400v1-max-250x250.png" ], [ [ 450, 282 ], "assets/images/resized/0004/3400/43400v1-max-450x450.png" ] ], "attribution" : null } ], "external_links" : [], "partners" : [] }') "is_valid?";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    104
Current database: ssb_json

+-----------+
| is_valid? |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)