title: MySql JSON nested array value comparation (greater / lower) in WHERE
body: <p>MySQL 5.7</p>
  
  <p>MySQL JSON field contains key "a" which contains array of JSON objects with keys "c" and it values. How to format JSON_EXTRACT path parameter to get greater compare (greater or lower) those "c" values with @v in WHERE part of SELECT statement? </p>
  
  <p>If we compare key "d" ( '$.d') > @v ) for different values of @v it will work like a charm, but it never work the same way with nested array object key.</p>
  
  <p>If we try to compare key "c" values of each object in array, it always return true '$.a[*].c') > @v in case that there are ARRAY and INTEGER types comparing. According to MySQL manual, INTEGER have lower priority then ARRAY type. </p>
  
  <p>The question is how to compare each value of "c" with @v and find only records with JSON field, which contains a greater or lower values of key "c".</p>
  
  <pre><code>SET @v = 70;
  SELECT JSON_EXTRACT(
      JSON_OBJECT(
          'a', 
          JSON_ARRAY(
              JSON_OBJECT('c', 25),
              JSON_OBJECT('c', 70), 
              JSON_OBJECT('c', 35),
              JSON_OBJECT('c', 30),
              JSON_OBJECT('c', 17.5),
              JSON_OBJECT('c', 
                  JSON_ARRAY(
                      JSON_OBJECT('c', 35),
                      JSON_OBJECT('c', 30)
                  )
              )
          ),
          'b',
          70
      ),
  '$.a[*].c') &gt; @v
  ;
  </code></pre>
  
  <p>To make a question clear let's search the records with prices greater then $70 in the next record-set:</p>
  
  <pre><code>CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` json NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  INSERT INTO `test` (`data`) VALUES 
      ('
          {
              "node": 1,
              "orders": [
                  {
                      "ts": "Jan 13, 2016 04:38pm",
                      "order_id": 1,
                      "products": [
                          {
                              "Qty": 1,
                              "SKU": 1,
                              "price": 40,
                              "total": 40
                          },
                          {
                              "Qty": 2,
                              "SKU": 2,
                              "price": 80,
                              "total": 160
                          },
                          {
                              "Qty": 5,
                              "SKU": 3,
                              "price": 10,
                              "total": 50
                          }
                      ]
                  }
              ]
          }
      '),
      ('
          {
              "node": 2,
              "orders": [
                  {
                      "ts": "Jan 13, 2016 04:38pm",
                      "order_id": 2,
                      "products": [
                          {
                              "Qty": 3,
                              "SKU": 1,
                              "price": 40,
                              "total": 120
                          },
                          {
                              "Qty": 1,
                              "SKU": 3,
                              "price": 10,
                              "total": 10
                          }
                      ]
                  }
              ]
          }
      ');
  </code></pre>
  
  <p>Select statement that works:</p>
  
  <pre><code>SELECT * FROM `test` 
  WHERE `data`-&gt;'$.node' &gt; 1;
  </code></pre>
  
  <p>and that should works, but don't:</p>
  
  <pre><code>SELECT * FROM `test` 
  WHERE `data`-&gt;'$.orders[*].order_id' &gt; 1;
  
  SELECT * FROM `test` 
  WHERE `data`-&gt;'$.orders[*].products[*].price' &gt; 50;
  
  SELECT * FROM `test` 
  WHERE `data`-&gt;'$.orders[*].products[*].total' &lt; 30;
  </code></pre>