Invalid JSON text in argument 2 - json_contains in MySQL 5.7.8

mysql json mysql-5.7
Alex Beals picture Alex Beals · Nov 4, 2015 · Viewed 21.3k times · Source

I have a database with one column that is JSON of strings (ex. ["ART","LIT"], etc.). I want to search it using json_contains.

However, when I try: 

json_contains(\`column_name`,"ART")

It errors saying:

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0 in 'ART'.

Note that json_contains doesn't error with numbers in the place of "ART", just with strings. Any idea what I can do to fix/get around this?

Answer

Alex Beals picture Alex Beals · Nov 4, 2015

Apparently, it treats integers differently from strings. While json_contains(`column_name`,"1") is a valid call, to check if it contains "ART", you must use json_contains(`column_name`,'"ART"').

That resolved my issue!

Related questions

How to update JSON data type column in MySQL 5.7.10?

I have started using MySQL 5.7.10 recently and I am liking the native JSON Data type a lot. But I ran into a problem when it comes to updating a JSON type value. Questions: Below is the table format, here I …

Read More
Alter an existing mysql column to a JSON data type

I am trying to change a MySQL column from varchar(9000) NULL to the new JSON data type in MySQL 5.7. The column holds valid JSON strings but some values are null. When I try the following: alter table log modify request …

Read More
MySQL select where JSON field property has value

How to write a basic MySQL query that has a WHERE on a property within a JSON data-type field? I don't see basic where clause q for json fields on SO. Something like this, but of course these dont work: …

Read More