How to query JSON array in MYSQL with AND

Question

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.

score 6 · Accepted Answer · answered Mar 18 '18 at 06:25

6

Just use JSON_CONTAINS:

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

answered Mar 18 '18 at 06:25

Alexandr Kalashnikov

410
3
9

Does that work if the json would be {"name": John, "male": true, "checked": true}? – ykay says Reinstate Monica Mar 18 '18 at 10:50
Yes. Its not depend on another fields or fields order in json/request. – Alexandr Kalashnikov Mar 19 '18 at 13:20
Amazing, thanks! First time someone ever took the time to answer one of my old questions so I really appreciate it! – ykay says Reinstate Monica Mar 19 '18 at 14:40

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

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.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 13 '17 at 16:02

Gregory

207
2
12

If you take a look at the question you'll see that I said the JSON object may have more keys and they may be in a different order. – ykay says Reinstate Monica Jan 14 '17 at 17:45
So you did, hopefully my example of how you can match JSON using SQL illustrates the difficulty with it. I linked to two threads that have detail on the issues, they tend to advice using noSQL instead. I'm less familiar with mySQL 5.7, but there is a link. I didn't want your question to be left completely passed by. – Gregory Jan 15 '17 at 19:14
Thanks, but I am wondering if there is a way to use mySQL functions for the select when any amount of keys can be in any order. – ykay says Reinstate Monica Jan 16 '17 at 10:05
I've added a 2nd SQL snippet. Essentially you're better off: just getting the row if it contains `"name": "John"` then using code/script to decode the json; OR not storing json in mySQL and instead create a relational database with more tables (or using a noSQL solution). Only you know the solution you want to work on. – Gregory Jan 16 '17 at 10:11
The problem with that snippet is that it will find rows where John is false but Lucy is true – ykay says Reinstate Monica Jan 16 '17 at 10:52

ykay says Reinstate Monica · Answer 3 · 2018-03-19T14:40:35.293

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)

How to query JSON array in MYSQL with AND

3 Answers3