Search code examples
jsonmariadbmysql-json

MariaDB 10.2 Query with JSON

I have some trouble with my query;

SELECT *, JSON_VALUE(cms_routing_data, "$.cms_routing_date.field") AS order_row FROM database.cms_routing WHERE cms_routing_module = 'events' AND cms_routing_data != '' AND order_row >= '2018-05-11' ORDER BY order_row ASC LIMIT 0,4

My database looks like; 

CREATE TABLE `cms_routing` (
  `cms_routing_id` int(10) NOT NULL,
  `cms_routing_module` varchar(50) DEFAULT NULL,
  `cms_routing_data` longblob DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My cms_routing_data looks like;

a:1:{s:16:"cms_routing_date";a:1:{s:5:"field";s:10:"2018-04-29";}}

PHPMyAdmin response;

#1054 - Onbekende kolom 'order_row' in where clause

Does anyone have experience with this and can help me?

Solution

  • There where two problems;

    1. Replace order_row in the WHERE clause with the respective expression, JSON_VALUE(cms_routing_data, "$.cms_routing_date.field").
    2. cms_routing_data wasn't valid JSON for MariaDB, it was serialized.

    A helpful tool was dbfiddle to find out.

    Thanks to @sticky-bit and @wchiquito.