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.

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.

Profiles Table

Search value in JSON array through MySQLi Query

Now we have enough data to write and run queries that fetch rows as 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 where clause that contains MySQLi’s function JSON_CONTAINS().

Search string values in JSON array

JSON_CONTAINS() function accepts two arguments in which 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.

Search Integer Values in JSON array

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

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

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