Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I have inserted records in mysql DB, with
json encoded
data type, Now I have to make search within
json encoded
data, but i am not able to get proper data using following MySql query.
SELECT `id` , `attribs_json`
FROM `products`
WHERE `attribs_json` REGEXP '"1":{"value":[^"3"$]'
Query results are key equal to "1" and value is anything except "3"
My data is:
{"feature":{"1":{"value":"["2","3"]"},
"2":{"value":["1"]},
"5":{"value":""},
"3":{"value":["1"]},
"9":{"value":""},
"4":{"value":"\u0633\u0627\u062a\u0646"},
"6":{"value":""},
"7":{"value":""},
"8":{"value":""}
"show_counter":"0",
"show_counter_discount":""
–
–
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
–
–
–
–
–
I use this query
SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])key_word([^"])"';
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';
The first query I use it to search partial value. The second query I use it to search exact word.
–
–
Storing JSON in database violates the first normal form.
The best thing you can do is to normalize and store features in another table. Then you will be able to use a much better looking and performing query with joins.
Your JSON even resembles the table.
Mysql 5.7 has builtin JSON functionality:
http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/
Correct pattern is:
WHERE `attribs_json` REGEXP '"1":{"value":[^}]*"3"[^}]*}'
[^}] will match any character except }
–
–
–
For Mysql8->
Query:
SELECT properties, properties->"$.price" FROM book where isbn='978-9730228236' and JSON_EXTRACT(properties, "$.price") > 400;
Data:
mysql> select * from book\G;
*************************** 1. row ***************************
id: 1
isbn: 978-9730228236
properties: {"price": 44.99, "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon"}
1 row in set (0.00 sec)
–
city.district,
city.info,
JSON_EXTRACT( city.info, "$.Population" ) AS 'formated_population'
INNER JOIN country ON city.CountryCode = country.
CODE INNER JOIN countrylanguage ON country.CODE = countrylanguage.CountryCode
GROUP BY
city.NAME,
city.district,
country.NAME;
ORDER BY
country.NAME ASC;
Search partial value:
SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])*key_word([^"])*"';
Search exact word:
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';
–
for MySQL all (and 5.7)
SELECT LOWER(TRIM(BOTH 0x22 FROM TRIM(BOTH 0x20 FROM SUBSTRING(SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed)))),LOCATE(0x22,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed))))),LENGTH(json_filed))))) AS result FROM `table`;
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
