CODEWITHSUNDEEP
mysqljson
ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4862

How to query JSON array in MYSQL with AND

If i have json like the following in a column in a mysql database

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

how can I select in mysql all rows where in the same object name = 'John' and checked = true.

The json objects may have more keys and keys may not be in a specific order.

Upvotes: 3

Views: 7140

Answers (3)

ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4862

Here is how it can be done in postgresql: 

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)

Upvotes: 0

Alexandr Kalashnikov
Alexandr Kalashnikov

Reputation: 410

Just use JSON_CONTAINS:

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

Upvotes: 6

Gregory
Gregory

Reputation: 207

You can try to match the string if the keys are always in the same order. Assuming your column is called people

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

With the knowledge of this solution, you could create a shorter SQL such as the following. You may use this alone, or use it as a subquery within the where clause of a query that will return all the rows.

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

You can probably see in this that JSON is not ideal for storing in mySQL.

The better solution is to design your database as a relational one, i.e. have an additional table called people and a column(s) that link the data. Saying how to design this would require me to know much more about your data/subject, but you should learn about SQL "joins" and normalisation.

There are other questions that discuss JSON in mySQL, such as Storing JSON in database vs. having a new column for each key and Storing Data in MySQL as JSON

As of mySQL 5.7 there are some json related functions. See this wagon article, and the mySQL documentation.

Upvotes: 0

Related Questions