How to search JSON data in MySQL

Question

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    quot 1 quot    quot value quot     quot 3 quot      Query results are key equal to  quot 1 quot  and value is anything except  quot 3 quot  My data is    quot feature quot    quot 1 quot    quot value quot   quot   quot 2 quot   quot 3 quot   quot                 quot 2 quot    quot value quot    quot 1 quot                  quot 5 quot    quot value quot   quot  quot                 quot 3 quot    quot value quot    quot 1 quot                  quot 9 quot    quot value quot   quot  quot                 quot 4 quot    quot value quot   quot  u0633 u0627 u062a u0646 quot                 quot 6 quot    quot value quot   quot  quot                 quot 7 quot    quot value quot   quot  quot                 quot 8 quot    quot value quot   quot  quot                  quot show counter quot   quot 0 quot    quot show counter discount quot   quot  quot

User · Answer

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

User · Answer

I think     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       lt    key word    gt

User · Answer

If your are using MySQL Latest version following may help to reach your requirement   select   from products where attribs json- gt    feature value     in  1 3

User · Answer

For Mysql8- gt  Query  SELECT properties  properties- gt  quot   price quot  FROM book where isbn  978-9730228236  and  JSON EXTRACT properties   quot   price quot    gt  400   Data  mysql gt  select   from book G                              1  row                             id  1 isbn  978-9730228236 properties    quot price quot   44 99   quot title quot    quot High-Performance Java Persistence quot    quot author quot    quot Vlad Mihalcea quot    quot publisher quot    quot Amazon quot   1 row in set  0 00 sec

User · Answer

I use this query  SELECT id FROM table name WHERE field name REGEXP   key name         key word          or SELECT id FROM table name WHERE field name RLIKE   key name       lt    key word    gt          The first query I use it to search partial value  The second query I use it to search exact word

User · Answer

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    gt  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

User · Answer

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

[mysql] How to search JSON data in MySQL?

Examples related to mysql

Examples related to json

Examples related to mysql-json