MariaDB 10.2 Query with JSON

Question

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?

"Onbekende kolom" seems to mean "unknown column" (Dutch?). There is indeed no column `order_row` in the table. It's just a column alias in your query and you cannot use column aliases in the `WHERE` clause. That's what the system is trying to tell you. Replace `order_row` in the `WHERE` clause with the respective expression, `JSON_VALUE(cms_routing_data, "$.cms_routing_date.field")`, and try again. — sticky bit, May 09 '18 at 00:02
@stickybit Thank you. Yes, it was Dutch, Sorry. I don't have MySQL errors with the new query so that looks good. But unfortunately, I don't get any results. It fails inside the cms_routing_data. — J Quest, May 09 '18 at 19:31
I tested my JSONPath Syntax with http://jsonpath.com/ and this gave me the correct result. But the same JSONPath inside my query doesn't return any results When you look closely at the query and my example cms_routing_data you will notice that there will be no match based on the example. But the real database I'm testing it does have a lot of records who would match but doesn't. — J Quest, May 09 '18 at 19:54
Is `cms_routing_data` a valid JSON for MariaDB?, see [dbfiddle](https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=5fb435ca014f3c98d634a75df95b3a92). — wchiquito, May 10 '18 at 09:31
Thank you for your answer; It wasn't valid JSON. I had to deserialize it. Also, the tool dbfiddle was very helpful for this. — J Quest, May 10 '18 at 10:07

score 0 · Accepted Answer · answered May 10 '18 at 10:13

There where two problems;

Replace order_row in the WHERE clause with the respective expression, JSON_VALUE(cms_routing_data, "$.cms_routing_date.field").
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.

MariaDB 10.2 Query with JSON

1 Answers1