Looping through an array in SQL column

Question

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.

What's the data type of that column? – Sergio Tulentsev Jan 06 '22 at 16:31 — Sergio Tulentsev, Jan 06 '22 at 16:31
Datatype of "Value" column is "longtext". – pomaaa Jan 06 '22 at 16:34 — pomaaa, Jan 06 '22 at 16:34
What does `SELECT VERSION();` return? – Bill Karwin Jan 06 '22 at 16:37 — Bill Karwin, Jan 06 '22 at 16:37
SELECT VERSION(); returns the following: 5.7.36-cll-lve – pomaaa Jan 06 '22 at 16:39 — pomaaa, Jan 06 '22 at 16:39

score 2 · Accepted Answer · answered Jan 06 '22 at 16:41

2

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.

answered Jan 06 '22 at 16:41

forpas

160,666
10
38
76

Looping through an array in SQL column

1 Answers1