キー値に基づいて MySQL で JSON オブジェクトを効率的に取得する方法

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

MySQL で JSON データを操作する場合、特定のキー値に基づいてオブジェクトを見つけるのは困難な場合があります。これは、従来の方法では配列インデックスや複雑な抽出技術の知識が必要であるためです。

JSON_TABLE() から Rescue

幸いなことに、MySQL 8.0 では JSON_TABLE() 関数が導入されました。 、このプロセスが簡素化されます。次のスキーマを考えてみましょう:

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;

データは次のように挿入されます:

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"}]'
);

キー「1」を指定して文字列「Grass」を検索するには、次のクエリを使用できます。 :

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;

このクエリは、目的のオブジェクトを返します:

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

代替: 従来のテーブル構造

JSON_TABLE() は、解決策を繰り返し使用するのは面倒になる可能性があります。別のアプローチは、次のように、テキストと値に個別の列を持つ従来のテーブル構造にデータを格納することです。

CREATE TABLE field_options (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  text varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  value varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

データは次のように挿入されます。

INSERT INTO field_options (text, value) VALUES
('Grass', '1'),
('Synthetic (New Type - Soft)', '2'),
('Synthetic  (Old Type - Hard)', '3'),
('Gravel', '5'),
('Clay', '6'),
('Sand', '7'),
('Grass/Synthetic Mix', '8');

この構造を使用すると、検索がより簡単になります:

SELECT * FROM field_options WHERE value = '1';

このアプローチにより、複雑な JSON クエリの必要性がなくなり、データの取得がより簡単になります。

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