Query a JSON column with an array of object in MySQL
I use mysql 5.7 and so JSON_CONTAINS can be easily used like this: SELECT JSON_CONTAINS( ‘[{“id”: “24av”,”name”: “she”},{“id”: “e0c2”, “name”: “another_she”}]’, JSON_OBJECT(‘id’, “e0c2”) );
mysql-json
MySQL 5.7.12 import cannot create a JSON value from a string with CHARACTER SET ‘binary’
You can apply a regex to the SQL text which you exported which will convert your binary strings into an insertable format. This was my quick and dirty fix when I faced this issue (X'[^,\)]*’) CONVERT($1 using utf8mb4) Applying this regex means INSERT INTO json_table (json_column) VALUES (X’7B22666F6F223A2022626172227D’); will now become INSERT INTO json_table (json_column) … Read more
Should I use blob or text for JSON in MySQL?
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 … Read more
JSON vs. Serialized Array in database [closed]
JSON encode() & decode() PHP Version >= 5.0.0 Nesting Limit of 20. PHP Version >= 5.2.3 Nesting Limit of 128. PHP Version >= 5.3.0 Nesting Limit of 512. Small footprint vs PHP’s serialize’d string. serialize() & unserialize() PHP Version >= 4.0.0 Methods are not lost on PHP Datatype Object. __wakeup() magic method called on any … Read more
How to update JSON data type column in MySQL 5.7.10?
Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it. mysql> select * from t1; +—————————————-+——+ | data | id | +—————————————-+——+ | {“key1”: “value1”, “key2”: “VALUE2”} | 1 | | {“key2”: “VALUE2”} | 2 | | {“key2”: “VALUE2”} | 1 | | {“a”: “x”, “b”: “y”, “key2”: … Read more
How to search JSON array in MySQL?
You may search an array of integers as follows: JSON_CONTAINS(‘[1,2,3,4,5]’,’7′,’$’) Returns: 0 JSON_CONTAINS(‘[1,2,3,4,5]’,’1′,’$’) Returns: 1 You may search an array of strings as follows: JSON_CONTAINS(‘[“a”,”2″,”c”,”4″,”x”]’,'”x”‘,’$’) Returns: 1 JSON_CONTAINS(‘[“1″,”2″,”3″,”4″,”5”]’,'”7″‘,’$’) Returns: 0 Note: JSON_CONTAINS returns either 1 or 0 In your case you may search using a query like so: SELECT * from my_table WHERE JSON_CONTAINS(data, ‘2’, … Read more
How to search JSON data in MySQL?
If you have MySQL version >= 5.7, then you can try this: SELECT JSON_EXTRACT(name, “$.id”) AS name FROM table WHERE JSON_EXTRACT(name, “$.id”) > 3 Output: +——————————-+ | name | +——————————-+ | {“id”: “4”, “name”: “Betty”} | +——————————-+ Please check MySQL reference manual for more details: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html