0

I am trying to select rows from a JSON table column (in MySQL 5.7.3) that meet a certain condition. And yes, using a JSON column rather than just creating a separate MySQL table is intentional. Consider the following MySQL table:

Table: Record

CREATE TABLE Record (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    foo VARCHAR(3) DEFAULT 'bar',
    data JSON DEFAULT NULL <--- WE POPULATE THIS COLUMN WITH DATA
)

Create a 'Record' with the following data

[{
    "id": 1,
    "timestamp": 1623671469
},
{
    "id": 2,
    "timestamp": 1623672921
},
{
    "id": 3,
    "timestamp": 1623872921
}]

Result

+---------------------------------------+
| Record [id,foo,data]                  |
+---------------------------------------+
| [1, 'bar', [{"id":1,"timestamp":162..]|
+---------------------------------------+

Now we want to select rows in the data column where id is larger than 2

SELECT
    JSON_EXTRACT(data, "$[*].id") as result
FROM
    Record
WHERE
    JSON_EXTRACT(data, "$[*].id") > 2 <--- does not work, returns [1,2,3] for any number

Similarly, we want to be able to return the rows that have a timestamp between timestamp1 and timestamp2.

Is it possible to select only those rows that meet a condition using JSON_EXTRACT (in MySQL 5.7.3)?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Gijs
  • 165
  • 1
  • 9
  • 1
    Just one more reason why the JSON datatype shouldn't be used. This is easy if you normalize the schema. – Barmar Jun 14 '21 at 13:45
  • *Create a 'Record' with the following data .. Result* - give this as INSERT INTO statement. *Now we want to select **rows in the data** column where id is larger than 2* - this means that you must parse an array in `data` to separate objects, one object per row, firstly. And then - check them separately. – Akina Jun 14 '21 at 13:51
  • Out of curiosity, why did you choose to put this data in JSON format instead of making a normal table with colums `id`, `timestamp`? – Bill Karwin Jun 14 '21 at 13:57
  • @Barmar, the JSON column adds flexibility for cases when additional columns are not known to you up front, (e.g. when user input should add to the existing columns) rather than creating columns in the table to cover every case – Gijs Jun 14 '21 at 14:05
  • You can do that with an attribute-value table. – Barmar Jun 14 '21 at 14:06
  • Anyway, I don't think there's a way to filter JSON based on a condition like this. `JSON_EXTRACT` returns all the values, and you're comparing that array with a single number. – Barmar Jun 14 '21 at 14:07

0 Answers0