Search code examples
mysqlarraysselectwhere-clausemysql-json

Looping through an array in SQL column

I have a SQL table that looks something like this:

| ID  | Value                                                 |
| --- | ----------------------------------------------------- |
| 1   | {"name":"joe", "lastname":"doe", "age":"34"}          |
| 2   | {"name":"jane", "lastname":"doe", "age":"29"}         |
| 3   | {"name":"michael", "lastname":"dumplings", "age":"40"}|

How can I using SQL select function, select only the rows where "age" (in value column) is above 30?

Thank you.

Solution

  • The column Value as it is it contains valid JSON data.
    You can use the function JSON_EXTRACT() to get the the age and convert it to a numeric value by adding 0:

    SELECT *
FROM tablename
WHERE JSON_EXTRACT(Value, "$.age") + 0 > 30;

    See the demo.