MySQL で JSON タイプのフィールドを使用する方法

テスト環境: MySQL8.0.19

準備

CREATE TABLE json_demo ( 
	`id` INT ( 11 ) NOT NULL PRIMARY KEY, 
	`content` json NOT NULL 
);
INSERT INTO json_demo ( id, content )
VALUES
	/*这条是数组*/
	( 1, '[{"key": 1, "order": 1, "value": "34252"},{"key": 2, "order": 2, "value": "23423"}]' ),
	/*这条是数组*/
	( 2, '[{"key": 4, "order": 4, "value": "234"},{"key": 5, "order": 5, "value": "234324523"}]' ),
	/*这条是对象*/
	( 3, '{"key": 3, "order": 3, "value": "43242"}' ),
	/*这条是对象*/
	( 4, '{"key": 6, "order": 6, "value": "5423"}' );

JSONオブジェクトの基本操作

指定したフィールド値をクエリする

/* 基础查询 */
SELECT
	content -> '$.key' AS 'key',
	JSON_EXTRACT(content, '$.key') AS 'key2',
	content -> '$.value' AS 'value',
	JSON_EXTRACT(content, '$.value') AS 'value2',
	content ->> '$.value' AS 'value3',
	JSON_UNQUOTE(JSON_EXTRACT(content, '$.value')) AS 'value4'
FROM
	json_demo 
WHERE
	id > 2;

TIPS:

• ##-> および ->> は MySQL によって設計された構文ですのうち、-> は MySQL5.7 でサポートされ、->> は MySQL8.0 でサポートされます。

• -> JSON_EXTRACT() と同等で、クエリ フィールドが文字列の場合、戻り値にも "" が含まれます。

• ->> JSON_UNQUOTE(JSON_EXTRACT()) と同等で、クエリ フィールドが文字列の場合、戻り値には "" が含まれません。

条件付きクエリに使用されます

content -> '$.key' はフィールドと見なすことができ、実行できる操作は次のとおりです。フィールド上で行われることは基本的に彼が行うことができます。

SELECT
	id,
	content -> '$.key' AS 'key',
	content ->> '$.value' AS 'value3'
FROM
	json_demo 
WHERE
	id > 2
	AND content -> '$.key' > 1
	AND content -> '$.value' like '%2%';

指定されたフィールド値を変更します

/* 修改 */
UPDATE json_demo 
SET content = JSON_REPLACE(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* JSON_SET也可以 */
UPDATE json_demo 
SET content = JSON_SET(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* 查询修改结果 */
SELECT id,content,content -> '$.key' AS 'key',content ->> '$.value' AS 'value3'
FROM json_demo WHERE id = 3;
/* 重新赋值 */
UPDATE json_demo SET 
content = JSON_REPLACE(content,'$.key',3,'$.value','43242') WHERE id = 3;

TIPS:

• JSON_REPLACE と JSON_SET は両方とも、特定のフィールドの値を変更するために使用できます。違いは、JSON_REPLACE が存在しない属性を置き換える場合、操作は無効になるのに対し、JSON_SET は存在しない属性を挿入することです。 。

• つまり、JSON_INSERT と同様に、JSON_SET を使用して属性を追加することもできます。違いは、既存の属性が挿入されると JSON_INSERT 操作が失敗するのに対し、JSON_SET はそれを置き換えることです。

#要素の追加

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.key', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.temp', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_SET(content, '$.temp2', 432)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

##JSON 配列操作

クエリ仕様フィールドvalue

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value',
	content -> '$[0].key' AS 'key2',
	content ->> '$[0].value' AS 'value2',
	/* 查询数组长度 */
	JSON_LENGTH(content) AS 'length'
FROM
	json_demo 
WHERE
	id < 3;

は条件付きクエリに使用されます

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value'
FROM
	json_demo 
WHERE
	id < 3
	/* content.value的值中存在like&#39;%34%&#39;的值 */
	AND content ->> &#39;$[*].value&#39; like &#39;%34%&#39;
	/* content.key的值中有4 */
	AND JSON_OVERLAPS(content ->> &#39;$[*].key&#39;, &#39;4&#39; );

指定されたフィールド値を変更します。

基本的な操作は JSON オブジェクトとあまり変わりません。つまり、対応するインデックス ビット '$[0]' を '$' の後に追加し、すべての '$ を指定します。 [*]」。配列に配列が含まれている場合は、「$[1][2][3]」を通じて深い配列要素を指定できます。

要素の追加

JSON_ARRAY_APPEND と JSON_ARRAY_INSERT の両方で配列要素を追加できます。違いは、JSON_ARRAY_APPEND ではインデックス ビットを指定する必要がなく、その場合は最後の位置に追加されますが、JSON_ARRAY_INSERT ではインデックス ビットを指定する必要があり、指定しない場合はエラーが報告されます。

JSON_ARRAY_APPEND は指定されたインデックス ビットの後に追加され、JSON_ARRAY_INSERT は指定されたインデックス ビットの前に挿入されます。

その他の操作

##JSON_ARRAY_APPEND()JSON_ARRAY_INSERT( ) JSON 配列への挿入JSON_CONTAINS()JSON ドキュメントのパスに特定のオブジェクトが含まれているかどうかJSON_CONTAINS_PATH()JSON ドキュメントのパスにデータが含まれているかどうかJSON_DEPTH()JSON の最大深さdocumentJSON_EXTRACT()JSON ドキュメントからデータを返すJSON_INSERT()データを挿入JSON ドキュメントへJSON_KEYS()JSON ドキュメント内のキー配列JSON_LENGTH() JSON ドキュメント内 要素数 JSON_MERGE() (非推奨) 重複キーを保持したまま、JSON ドキュメントをマージします。 JSON_MERGE_PRESERVE() の非推奨の同義語 JSON ドキュメントをマージし、重複キーの値を置き換えます重複キーを保持しながら JSON ドキュメントをマージしますJSON オブジェクトを作成します 2 つの JSON ドキュメントを比較し、共通のキーと値のペアまたは配列要素がある場合は TRUE (1) を返し、そうでない場合は FALSE (0) を返します JSON ドキュメントを読みやすい形式で印刷##JSON_QUOTE()参照 JSON ドキュメントJSON_REMOVE()JSON ドキュメントからデータを削除JSON_REPLACE()

名前	説明
JSON_ARRAY()	JSON 配列の作成
##JSON ドキュメントへのデータの追加
	#JSON_MERGE_PATCH()
	JSON_MERGE_PRESERVE()
	JSON_OBJECT()
	JSON_OVERLAPS() (8.0.17 で導入)
	JSON_PRETTY()
	## JSON ドキュメント内の値を置換
JSON_SCHEMA_VALID() (8.0.17 で導入)	JSON スキーマに対して JSON ドキュメントを検証します。ドキュメントがスキーマの場合は TRUE/1 を返し、それ以外の場合は FALSE/0 を返します。
JSON_SCHEMA_VALIDATION_REPORT() (8.0.17 で導入)	JSON スキーマに対して JSON ドキュメントを検証し、成功を含む検証結果に関するレポートを JSON 形式で返します。または失敗と失敗理由
JSON_SEARCH()	JSON ドキュメント内の値のパス
#JSON_SET()	JSON ドキュメントにデータが挿入されます
JSON_STORAGE_FREE()	部分更新後の JSON 列値のバイナリ表現の空き領域
JSON_STORAGE_SIZE()	JSON ドキュメントのバイナリ表現を保存するために使用されるスペース
JSON_TABLE()	戻り値のデータリレーショナル テーブルとしての JSON 式から
#JSON_TYPE()	JSON 値の型
JSON_UNQUOTE()	JSON 値の逆参照
JSON_VALID()	JSON 値が有効かどうか
#JSON_VALUE() (8.0 で導入) .21)	指定されたパスが指す場所にある JSON ドキュメントから値を抽出し、この値を VARCHAR (512) または指定された型
として返します。 MEMBER OF() (8.0.17 導入)	最初のオペランドが 2 番目のオペランドとして渡された JSON 配列の要素と一致する場合は true (1) を返し、それ以外の場合は false (0) を返します

以上がMySQL で JSON タイプのフィールドを使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。