How to search value in JSON array through MySQLi query?

Sometimes in large-scale applications, we save data in the JSON array in the database. This way allows storing data without adding columns to the table.

Let’s suppose we have a profiles table will the following columns.

• id
• status
• user_data

We store user profiles JSON array into profiles table. Following are the example of JSON array that we store.

[
  {
    "balance": "2343",
    "picture": "http://placehold.it/1sw12",
    "age": 23,
    "name": "John Kim",
    "gender": "male",
    "company": "xyz com",
    "email": "johnkim@example.com"
  }
]

The profile table has many users’ data in JSON that follow the same format as above.

Search value in JSON array through MySQLi Query

We now have enough data to write and run queries that fetch rows per demand. Let’s write a query to fetch a row by searching the name in the JSON array.

SELECT * FROM `profiles` WHERE JSON_CONTAINS(user_data, '{"name": "sarah"}');

This select query has a where clause that contains MySQLi’s function JSON_CONTAINS().

JSON_CONTAINS() function accepts two arguments: the first is the column name and the second is the exact JSON key and its value that is needed to match.

Search Integer value in JSON array

In the last query we searched for a string value in the JSON array, now let’s search for an integer value. If you notice the user’s JSON array, the age key has an integer value.

SELECT * FROM `profiles` WHERE JSON_CONTAINS(user_data, '{"age": 23}');

This query fetches 23 years old users from the profiles table.

Additionally, we can add one more where clause to add condition if the user’s status is active or 1.

SELECT * FROM `profiles` WHERE JSON_CONTAINS(user_data, '{"age": 23}') AND `status` = 1

This query further checks if the row’s status has value 1 through the where clause.

Conclusion

This article explains searching for value in a JSON array. To do that we used the MySQL function JSON_CONTAINS() function to accept two arguments: the first is the column name and the second is the exact JSON key and its value that is needed to match.

So in this article, we learned to search values in a JSON array. Also, the following points have been covered

• JSON_CONTAINS() definition
• Search string values in JSON Array
• Search integer values in JSON Array

---

Written by

Ayaz Ali Shah

I am a skilled full-stack developer with extensive experience in creating and deploying large and small-scale applications. My expertise spans front-end and back-end technologies, along with database management and server-side programming.

---

---

Related Posts

• 1. MySQL SELECT Statement
• 2. How to Copy Rows and Insert Them into Same Table Using MySQLi?
• 3. Best Way to Create Reliable Database Design for Multi-Language