MySQL テーブルの JSON フィールドからキー値によって特定のオブジェクトを取得する方法

キー値による MySQL JSON 内のオブジェクトの検索

シナリオ:

MySQL テーブルを作成し、特定のキー値を検索しながらテーブル内から特定のオブジェクトを取得したいと考えています。

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>

チャレンジ:

次の条件に基づいて、オプション フィールドから「草」のテキスト値を選択する必要があります。値「1」を検索します。

MySQL 8.0 のソリューション:

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>

このクエリは、クロス結合と JSON_TABLE() を使用して、オプション フィールド内の各オブジェクトのテキスト列と値列を取得します。

JSON_TABLE に関する懸念事項:

ただし、JSON_TABLE() の使用は複雑になる可能性があり、このようなクエリが必要になるたびに繰り返す必要があります。

推奨事項:

JSON を使用する代わりに、テキストと値のペア専用の列を持つテーブルにデータを保存するのが簡単になります。これにより、通常の SQL 構文

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

を使用した簡単な検索が可能になります。

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