如何使用和在MYSQL中查询JSON数组

Question

如果我在mysql数据库的列中有如下json

[
 {
  "name": "John",
  "checked": true
 },
 {
  "name": "Lucy",
  "checked": false
 }
]

如何在mysql中选择同一对象名为'John'且checked=true的所有行。

json对象可能有更多的键，并且键可能没有特定的顺序。

Answer 1

下面是如何在postgreql中完成：

create table users (data jsonb);'

insert into users values ('[{"name": "John", "checked": "true"}, {"name": "Lucy", "checked": "false"}]'),('[{"name": "John", "checked": "false"}, {"name": "Lucy", "checked": "false"}]'),('[{"name": "John", "checked": "false"}, {"name": "Lucy", "checked": "true"}]');

select * from users, jsonb_array_elements(users.data) obj
where obj->>'name' = 'John' and obj->>'checked' = 'true';


    data                                     |                value
-----------------------------------------------------------------------------+-------------------------------------
 [{"name": "John", "checked": "true"}, {"name": "Lucy", "checked": "false"}] | {"name": "John", "checked": "true"}
(1 row)

Answer 2

如果键的顺序始终相同，则可以尝试匹配字符串。假设您的专栏名为people

   SELECT
   IF(people LIKE '%[
     {
      \"name\": \"John\",
      \"checked\": true%', TRUE, FALSE) AS 'john_checked'
   FROM table
   WHERE (people LIKE '%[
     {
      \"name\": \"John\",
      \"checked\": true%')

有了这个解决方案的知识，您可以创建一个较短的SQL，如下所示。您可以单独使用它，也可以将其用作将返回所有行的查询的where子句中的子查询。

   SELECT
   IF(people LIKE '%\"checked\": true%', TRUE, FALSE) AS 'john_checked'
   FROM table
   WHERE (people LIKE '%\"name\": \"John\"%')

您可能会看到，JSON不适合存储在mySQL中。

更好的解决方案是将数据库设计为关系型数据库，即有一个名为people的附加表和一个链接数据的列。说到如何设计它，我需要更多地了解您的数据/主题，但您应该了解SQL“连接”和规范化。

还有其他问题，讨论JSON在MySQL中，如存储JSON在数据库与有一个新的列为每个键和存储数据在MySQL作为JSON

在MySQL 5.7中，有一些与json相关的函数。请参阅这篇文章和mySQL留档。

Answer 3

只需使用JSON，它包含：

SELECT * from `users`
WHERE JSON_CONTAINS(`data`, '{"name": "John","checked": true}');