title: Select rows from a MySQL JSON column that meets a condition
body: <p>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:</p>
  <p><strong>Table: Record</strong></p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Record (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      foo VARCHAR(3) DEFAULT 'bar',
      data JSON DEFAULT NULL &lt;--- WE POPULATE THIS COLUMN WITH DATA
  )
  
  </code></pre>
  <p><strong>Create a 'Record' with the following data</strong></p>
  <pre class="lang-json prettyprint-override"><code>[{
      &quot;id&quot;: 1,
      &quot;timestamp&quot;: 1623671469
  },
  {
      &quot;id&quot;: 2,
      &quot;timestamp&quot;: 1623672921
  },
  {
      &quot;id&quot;: 3,
      &quot;timestamp&quot;: 1623872921
  }]
  </code></pre>
  <p><strong>Result</strong></p>
  <pre><code>+---------------------------------------+
  | Record [id,foo,data]                  |
  +---------------------------------------+
  | [1, 'bar', [{&quot;id&quot;:1,&quot;timestamp&quot;:162..]|
  +---------------------------------------+
  </code></pre>
  <p>Now we want to select rows in the <strong>data</strong> column where <strong>id</strong> is <strong>larger than 2</strong></p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      JSON_EXTRACT(data, &quot;$[*].id&quot;) as result
  FROM
      Record
  WHERE
      JSON_EXTRACT(data, &quot;$[*].id&quot;) &gt; 2 &lt;--- does not work, returns [1,2,3] for any number
      
  </code></pre>
  <p>Similarly, we want to be able to return the rows that have a timestamp between <strong>timestamp1</strong> and <strong>timestamp2</strong>.</p>
  <p>Is it possible to select only those rows that meet a condition using JSON_EXTRACT (in MySQL 5.7.3)?</p>