MySQL, MariaDB - JSONデータ型を用いたSQL, DMLの無難な書き方

MySQLでは、SQLやDMLでJSONデータ型を扱う場合に、複数の記法が可能です。
私は、MySQLとMariaDBのいずれでも解釈可能という観点から、JSON_EXTRACTなどのJSON関数を用いるようにしています。

理由

JSON関数を用いない記法は、MariaDBでは解釈不可となる場合があります。このため、JSON関数を用いています。

実行例

最初はMySQLです。

hoge@localhost C:\Users\hoge>mysql -u hoge -p
mysql: Unknown OS character set 'cp0'.
mysql: Switching to the default character set 'utf8mb4'.
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use hoge
Database changed
mysql> SELECT *
    ->  FROM generalj
    ->  WHERE `json`->'$.devid' = 'hmi'
    ->          AND `json`->'$.production' = '00000001'
    ->          AND `json`->'$.part' = '007'
    ->          AND `json`->'$.machine' = 'AToM'
    ->  AND `json`->'$.command' = 'start'
    ->  ORDER BY timeinsert DESC;
Empty set (0.04 sec)

mysql> SELECT *  
    ->  FROM generalj  
    ->  WHERE JSON_EXTRACT(json, '$.devid') = 'hmi'
    ->          AND JSON_EXTRACT(json, '$.production') = '00000001'
    ->          AND JSON_EXTRACT(json, '$.part') = '007'  
    ->          AND JSON_EXTRACT(json, '$.machine') = 'AToM'
    ->          AND JSON_EXTRACT(json, '$.command') = 'start'  
    ->  ORDER BY timeinsert DESC;
Empty set (0.00 sec) 

mysql>

いずれも、検索結果がゼロ件ですが合っています。問題ではありません。
次はMariaDBです。

$ mysql -u hoge -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28867
Server version: 10.5.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use hoge
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hoge]> SELECT * 
    -> FROM generalj 
    -> WHERE `json`->'$.devid' = 'hmi' 
    -> AND `json`->'$.production' = '00000001' 
    -> AND `json`->'$.part' = '007' 
    -> AND `json`->'$.machine' = 'AToM' 
    -> AND `json`->'$.command' = 'start' 
    -> ORDER BY timeinsert DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$.devid' = 'hmi' 
AND `json`->'$.production' = '00000001' 
AND `json`->'$.p...' at line 3

MariaDB [hoge]> SELECT * 
    -> FROM generalj 
    -> WHERE JSON_EXTRACT(json, '$.devid') = 'hmi' 
    -> AND JSON_EXTRACT(json, '$.production') = '00000001' 
    -> AND JSON_EXTRACT(json, '$.part') = '007' 
    -> AND JSON_EXTRACT(json, '$.machine') = 'AToM' 
    -> AND JSON_EXTRACT(json, '$.command') = 'start' 
    -> ORDER BY timeinsert DESC;
Empty set (0.003 sec)
MariaDB [hoge]>

MySQLでは通用する「JSON関数を使わない記法」が、MariaDBではエラーとなります。

注意

上記実行例は、各DBMSのバージョンに応じて異なるかもしれません。