MySQL のJSON 型を扱うSQL

サンプル、

DELIMITER //
DROP TABLE IF EXISTS sakila.t_item
//
CREATE TABLE t_item(
  id                    INT  NOT NULL AUTO_INCREMENT
, name                  VARCHAR(40) NOT  NULL
, message               JSON DEFAULT NULL
, PRIMARY KEY (id)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
//
DELIMITER ;

TRUNCATE TABLE t_item;
INSERT INTO t_item( name, message ) VALUES
 ('オレンジ', '{ "price": 200, "limit": "2021-12-07" }')
,('レモン', JSON_OBJECT("price", 180, "limit", "2021-12-08"))
,('メロン', '{ "price": 1200, "limit": "2021-12-16" , "other":{ "label":"ABC", "width": 230 } }')
,('バナナ', '{ "price": 320, "limit": "2021-12-09" , "other":{ "label":"banana", "width": 180 } }')
,('Abc', null)
;
commit;

-> path記述 指定による参照

SELECT id, name, message->'$.limit' FROM t_item;

+----+----------+--------------------+
| id | name     | message->'$.limit' |
+----+----------+--------------------+
|  1 | オレンジ | "2021-12-07"       |
|  2 | レモン   | "2021-12-08"       |
|  3 | メロン   | "2021-12-16"       |
|  4 | バナナ   | "2021-12-09"       |
|  5 | Abc      | NULL               |
+----+----------+--------------------+

JSON_UNQUOTE で、-> path 指定による参照で
出力される (")ダブルクォーテーションを除去する

SELECT id, name, JSON_UNQUOTE(message->'$.limit')  FROM t_item;

+----+----------+----------------------------------+
| id | name     | JSON_UNQUOTE(message->'$.limit') |
+----+----------+----------------------------------+
|  1 | オレンジ | 2021-12-07                       |
|  2 | レモン   | 2021-12-08                       |
|  3 | メロン   | 2021-12-16                       |
|  4 | バナナ   | 2021-12-09                       |
|  5 | Abc      | NULL                             |
+----+----------+----------------------------------+

JSON_VALUE を使う
　（OracleDB にもこの JSON_VALUEという関数はある）

SELECT id, name, JSON_VALUE(message, '$.limit') FROM t_item;

+----+----------+--------------------------------+
| id | name     | JSON_VALUE(message, '$.limit') |
+----+----------+--------------------------------+
|  1 | オレンジ | 2021-12-07                     |
|  2 | レモン   | 2021-12-08                     |
|  3 | メロン   | 2021-12-16                     |
|  4 | バナナ   | 2021-12-09                     |
|  5 | Abc      | NULL                           |
+----+----------+--------------------------------+

JSON_CONTAINSを使う
　　JSON_CONTAINS(target, candidate[, path])

SELECT id, name,
 JSON_UNQUOTE(message->'$.limit')       AS itemLimit,
 JSON_UNQUOTE(message->'$.other.label') AS label,
 JSON_UNQUOTE(message->'$.other.width') AS width
 FROM t_item
 WHERE 1=1
  AND JSON_CONTAINS(message, '230','$.other.width')

+----+--------+------------+-------+-------+
| id | name   | itemLimit  | label | width |
+----+--------+------------+-------+-------+
|  3 | メロン | 2021-12-16 | ABC   | 230   |
+----+--------+------------+-------+-------+

JSON_CONTAINS_PATHを使う
　　JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

one を指定

SELECT id, name,
 JSON_UNQUOTE(message->'$.limit')       AS itemLimit,
 JSON_UNQUOTE(message->'$.other.label') AS label,
 JSON_UNQUOTE(message->'$.other.width') AS width
 FROM t_item
 WHERE 1=1
  AND JSON_CONTAINS_PATH(message, 'one', '$.other.width')

+----+--------+------------+--------+-------+
| id | name   | itemLimit  | label  | width |
+----+--------+------------+--------+-------+
|  3 | メロン | 2021-12-16 | ABC    | 230   |
|  4 | バナナ | 2021-12-09 | banana | 180   |
+----+--------+------------+--------+-------+

all を指定

SELECT id, name,
 JSON_UNQUOTE(message->'$.limit')       AS itemLimit,
 JSON_UNQUOTE(message->'$.other.label') AS label,
 JSON_UNQUOTE(message->'$.other.width') AS width
 FROM t_item
 WHERE 1=1
  AND JSON_CONTAINS_PATH(message, 'all', '$.limit', '$.other.width')

+----+--------+------------+--------+-------+
| id | name   | itemLimit  | label  | width |
+----+--------+------------+--------+-------+
|  3 | メロン | 2021-12-16 | ABC    | 230   |
|  4 | バナナ | 2021-12-09 | banana | 180   |
+----+--------+------------+--------+-------+