キーと値のペアに基づいて MySQL の JSON 列から特定のオブジェクトを取得する方法

MySQL JSON: キー値に基づくオブジェクトの検索

MySQL テーブルは、多くの場合、JSON カラムを利用して複雑なデータ構造を格納します。 JSON を使用する場合の一般的なタスクは、キーと値のペアに基づいて特定のオブジェクトを取得することです。ただし、オブジェクトを直接見つけるのは必ずしも簡単ではありません。

JSON スキーマの例:

次のスキーマを考えてみましょう:

<code class="sql">CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;</code>

サンプル付きデータ:

<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);</code>

問題:

オプションの JSON 配列から文字列「Grass」を取得し、その対応する「値」が「」であると仮定します。 1.」 JSON_EXTRACT() を単独で使用するには配列のインデックスが必要ですが、これはすぐには入手できない場合があります。

解決策 1: JSON_SEARCH() と恐ろしい文字列操作を使用する

このアプローチJSON_SEARCH() を組み合わせてインデックスを部分的に検索し、文字列から不要な部分を削除します。

<code class="sql">SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;</code>

解決策 2: JSON_TABLE() を使用する

MySQL 8.0 では JSON_TABLE が導入されています。 () このタスクを簡素化するには:

<code class="sql">select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;</code>

代替アプローチ: リレーショナル データ モデル

この例のような単純なデータ構造の場合は、データをリレーショナル テーブルに格納することを検討してください。キーと値のペアの列があり、直接検索が可能です:

<code class="sql">SELECT * FROM field_options WHERE value = '1';</code>

以上がキーと値のペアに基づいて MySQL の JSON 列から特定のオブジェクトを取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。