1

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.

Improve this question
6
  • What is the DBMS? Commented Jan 6, 2022 at 16:19
  • The DBMS used is MySQL Commented Jan 6, 2022 at 16:27
  • What's the data type of that column? Commented Jan 6, 2022 at 16:31
  • Datatype of "Value" column is "longtext". Commented Jan 6, 2022 at 16:34
  • What does SELECT VERSION(); return? Commented Jan 6, 2022 at 16:37

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.