SQL - JSON関数：入門ガイド

こんにちは、未来のデータベース魔法師たち！今日は、SQLとJSON関数の世界に楽しく飛び込んでみましょう。コードを書いたことがない人も心配しないでください。私はあなたの親切なガイドとして、ステップバイステップで進めていきます。このチュートリアルの終わりには、JSONデータをプロのように弄ぶことができるようになるでしょう！

SQL - JSON Functions

JSONとは？

SQL関数にどぶり込む前に、まずJSONについて話しましょう。JSONはJavaScript Object Notationの略で、データの保存と交換に使われる一般的な方法です。人間とコンピュータの両方にとって読みやすい形式で情報を整理する方法と考えられます。

例えば、以下は本についてのJSONデータです：

{
"title": "The SQL Adventure",
"author": "Jane Coder",
"year": 2023,
"genres": ["Technology", "Education"]
}

これを見て、どれほど整然としているかお分かりでしょうか？それがJSONの美しさです！

SQLでJSONを使う理由

さて、あなたはおそらく「なぜデータベースにJSONが必要なのか？」と思っているかもしれません。オンライン書店を運営しているとします。ある本は複数の著者もちろんですし、他の本は単一の著者だけです。また、ある本は多くのジャンルもちろんですし、他の本はわずかいくつかのジャンルだけです。JSONを使うことで、このような多様な情報を柔軟に保存できます。別々のテーブルを作成する必要はありません。

SQL JSON関数：あなたの新しい親友たち

さあ、私たちのSQL-JSONゲームのスター選手たちを紹介しましょう。これらの関数を使うことで、SQLデータベース内でJSONデータを扱うことができます。

1. JSON_VALUE()

この関数は宝探しのようです。JSONデータの中から特定の値を引き出します。

SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name') AS FirstName;

これが返すのは：

FirstName
--------
Alice

ここでは、SQLにJSONデータの中を見せて、「name」キーに関連する値を見つけてもらっています。

2. JSON_QUERY()

JSON_VALUEは単一の値を取得しますが、JSON_QUERYはオブジェクトや配列全体を取得できます。

SELECT JSON_QUERY('{"books": ["SQL 101", "JSON Mastery"]}', '$.books') AS BookList;

結果：

BookList
--------
["SQL 101", "JSON Mastery"]

この関数は、ネストされたJSON構造を抽出するのに最適です。

3. JSON_MODIFY()

この関数は、あなたの個人的なJSONエディタのようです。JSONデータ内の値を変更することができます。

DECLARE @json NVARCHAR(100) = '{"name": "Bob", "age": 35}';
SELECT JSON_MODIFY(@json, '$.age', 36) AS UpdatedJSON;

結果：

UpdatedJSON
-----------
{"name": "Bob", "age": 36}

Bobに誕生日を与えました！彼の年齢を35から36に変更しました。

4. ISJSON()

この関数は、JSON探偵のようです。文字列が有効なJSONかどうかを確認します。

SELECT ISJSON('{"name": "Charlie", "age": 40}') AS IsValidJSON;

結果：

IsValidJSON
-----------
1

1の返り値は「はい、これは有効なJSONです」という意味で、0は「いいえ、有効なJSONではありません」という意味です。

5. JSON_OBJECT()

この関数は、JSON工場のようです。SQLデータからJSONオブジェクトを作成します。

SELECT JSON_OBJECT('name': 'Dana', 'age': 28) AS PersonJSON;

結果：

PersonJSON
----------
{"name":"Dana","age":28}

これは、通常のSQLデータをJSON形式に変換するのに非常に良い方法です。

すべてをまとめて

さあ、私たちのJSON関数を紹介したので、実際のシナリオでどのように使えるか見てみましょう。先ほど話したオンライン書店を運営しているとします。

-- 書籍情報を保存するテーブルを作成
CREATE TABLE Books (
BookID INT PRIMARY KEY,
BookInfo NVARCHAR(MAX)
);

-- サンプルデータを插入
INSERT INTO Books (BookID, BookInfo)
VALUES
(1, '{"title": "SQL Basics", "author": "John Doe", "year": 2020, "genres": ["Technology", "Education"]}'),
(2, '{"title": "JSON and You", "author": "Jane Smith", "year": 2021, "genres": ["Technology"]}');

-- 書籍タイトルを取得するクエリ
SELECT
BookID,
JSON_VALUE(BookInfo, '$.title') AS Title
FROM Books;

-- すべてのジャンルを取得するクエリ
SELECT
BookID,
JSON_QUERY(BookInfo, '$.genres') AS Genres
FROM Books;

-- 書籍の年を更新
UPDATE Books
SET BookInfo = JSON_MODIFY(BookInfo, '$.year', 2023)
WHERE BookID = 1;

-- 書籍に新しいジャンルを追加
UPDATE Books
SET BookInfo = JSON_MODIFY(
BookInfo,
'$.genres',
JSON_QUERY(
CONCAT(
'[',
SUBSTRING(JSON_QUERY(BookInfo, '$.genres'), 2, LEN(JSON_QUERY(BookInfo, '$.genres')) - 2),
',"Programming"]'
)
)
)
WHERE BookID = 2;

-- BookInfoが有効なJSONか確認
SELECT
BookID,
ISJSON(BookInfo) AS IsValidJSON
FROM Books;

これで全部です！テーブルを作成し、JSONデータを插入し、特定の値をクエリし、JSONを更新し、甚至にJSON配列に新しい情報を追加しました。

結論

おめでとうございます！あなたは刚刚、SQL JSON関数の世界への最初の一歩を踏み出しました。これらの強力なツールを使うことで、SQLデータベース内で柔軟でネストされたデータ構造を扱うことができます。あなたがこの旅を続ける中で、これらの関数をさらに多くの方法で活用する手段を見つけるでしょう。

これらの概念をマスターする鍵は練習です。ですので、自分でJSONデータとSQLクエリを試してみてください。幸せなコーディングを、そしてあなたのデータベースが常に正規化されることを祈っています！

Credits: Image by storyset