JSON形式のデータから数字だけを簡単抽出！MariaDBでできるテクニック
2024-06-22
MariaDBで「user":"128"}" から数字のみを抽出する正規表現
手順
JSON_EXTRACT()関数を使用して、userフィールドの値を JSON オブジェクトから抽出します。- 抽出した値を文字列に変換します。
REGEXP()関数を使用して、抽出された文字列から数字のみを抽出します。
例
SELECT REGEXP_REPLACE(JSON_EXTRACT('{"user":"128"}', '$."user"'), '[^0-9]', '');
説明
JSON_EXTRACT('{"user":"128"}', '$."user"'): この部分は、{"user":"128"}という JSON オブジェクトから"user"フィールドの値を抽出します。REGEXP_REPLACE(JSON_EXTRACT('{"user":"128"}', '$."user"'), '[^0-9]', ''): この部分は、REGEXP_REPLACE()関数を使用して、抽出された文字列から数字以外のすべての文字を空文字に置き換えます。[^0-9]: この正規表現は、数字以外のすべての文字に一致します。
結果
このクエリを実行すると、次のような結果が得られます。
128
補足
- この例では、
PCRE正規表現エンジンを使用しています。MariaDB 10.0 以降では、PCREエンジンがデフォルトで有効になっています。 REGEXP()関数には、さまざまなオプションがあります。詳細については、MariaDB のドキュメントを参照してください。
-- サンプルデータ
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);
INSERT INTO users (data) VALUES
('{"user":"12345", "age":30}'),
('{"user":"abcde", "score":65}');
-- 数字のみを抽出
SELECT id, REGEXP_REPLACE(JSON_EXTRACT(data, '$."user"'), '[^0-9]', '') AS user_id
FROM users;
CREATE TABLE usersステートメント:usersという名前のテーブルを作成します。このテーブルには、id列とdata列があります。id列はプライマリ キーであり、自動的にインクリメントされます。data列は JSON 形式のデータです。INSERT INTO usersステートメント:usersテーブルに 2 つの行を挿入します。各行には、userフィールドとageまたはscoreフィールドを含む JSON オブジェクトが含まれます。SELECTステートメント:usersテーブルからすべての行を選択します。REGEXP_REPLACE(JSON_EXTRACT(data, '$."user"'), '[^0-9]', ''): この式は、data列の JSON オブジェクトから"user"フィールドの値を抽出し、数字以外のすべての文字を空文字に置き換えます。
id | user_id
---+--------
1 | 12345
2 | abcde
- このコードは、MariaDB 10.0 以降で使用できます。
usersテーブルの構造は、必要に応じて変更できます。- 正規表現パターン
[^0-9]は、数字以外のすべての文字に一致します。必要に応じて、このパターンを変更して、抽出する文字の種類を変更できます。
MariaDBで「user":"128"}" から数字のみを抽出するその他の方法
JSON_VALUE() 関数と SUBSTRING_INDEX() 関数を使用する
この方法は、JSON_VALUE() 関数を使用して JSON オブジェクトから "user" フィールドの値を抽出し、SUBSTRING_INDEX() 関数を使用して先頭から数字のみを抽出します。
SELECT SUBSTRING_INDEX(JSON_VALUE('{"user":"128"}', '$."user"'), '-', -1) AS user_id;
SUBSTRING_INDEX(JSON_VALUE('{"user":"128"}', '$."user"'), '-', -1): この部分は、SUBSTRING_INDEX()関数を使用して、抽出された文字列の先頭からハイフン (-) までの部分を抽出します。-1は、文字列の最後から抽出することを意味します。
CONVERT_JSON_PATH() 関数を使用する
SELECT CONVERT_JSON_PATH('{"user":"128"}', '$."user"') AS user_id;
- 正規表現 関数は、柔軟性と制御性に優れています。複雑なパターンを使用して、さまざまな種類のデータを抽出することができます。
- JSON_VALUE()関数とSUBSTRING_INDEX()` 関数 の組み合わせは、よりシンプルでわかりやすい方法です。
- **CONVERT_JSON_PATH()` 関数は、最も簡潔な方法ですが、柔軟性に欠けます。
regex mariadb
