Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

trifek's avatar

Mysql search in json table - like query

I am beginner in webdevelopment. I have this table:

CREATE TABLE `psTextPages` (
  `id_page` int(11) UNSIGNED NOT NULL,
  `page_type2` int(11) NOT NULL DEFAULT '0',
  `page_type3` int(11) NOT NULL DEFAULT '0',
  `page_type4` int(11) NOT NULL DEFAULT '0',
  `page_type5` int(11) NOT NULL DEFAULT '0',
  `id_category_page` int(11) NOT NULL DEFAULT '0',
  `id_category_page2` int(11) NOT NULL DEFAULT '0',
  `id_category_page3` int(11) NOT NULL DEFAULT '0',
  `id_category_page4` int(11) NOT NULL DEFAULT '0',
  `id_category_page5` int(11) NOT NULL DEFAULT '0',
  `id_categories_of_photos` int(11) NOT NULL DEFAULT '0',
  `author_add` int(11) NOT NULL DEFAULT '0',
  `author_update` int(11) NOT NULL DEFAULT '0',
  `system` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `page_type` int(11) NOT NULL DEFAULT '1',
  `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `title_on_the_list` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_of_creation` datetime NOT NULL,
  `visible_on_the_top_menu` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `visible_on_the_left_menu` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `visible_on_promo_box` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `title_pl` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description_pl` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
  `keywords_pl` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
  `short_content_pl` text COLLATE utf8_unicode_ci,
  `content_pl` longtext COLLATE utf8_unicode_ci,
  `url_address_pl` varchar(125) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` date NOT NULL,
  `file` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hideData` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `enable2` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `dateToShow` datetime DEFAULT NULL,
  `top_image_description` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `visible_image` tinyint(1) NOT NULL DEFAULT '0',
  `tags` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `psTextPages`
  ADD PRIMARY KEY (`id_page`);

ALTER TABLE `psTextPages`
  MODIFY `id_page` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

in tags I have json. For example:

[
    "tag 1",
    " tag 2",
    " tag 3",
    " tag4"
]

How can I search in this column? I need best possible performance to search in this table...

How can I make it?

Add index to column tags Is correct solutions? This column is not unique

0 likes
3 replies
LaryAI's avatar
LaryAI
AI
2 years ago
Level 58

MySQL has JSON functions that you can use to query JSON data. In your case, you can use the JSON_CONTAINS() function to search in the tags column. Here is an example:

SELECT * FROM psTextPages WHERE JSON_CONTAINS(tags, '"tag 1"');

This will return all rows where the tags column contains "tag 1".

As for indexing, MySQL does not support indexing on JSON columns directly. However, you can create a generated column that extracts a scalar value from the JSON column, and then index the generated column.

Here is an example:

ALTER TABLE psTextPages ADD COLUMN tags_extracted AS (JSON_UNQUOTE(JSON_EXTRACT(tags, '$[0]')));
CREATE INDEX idx_tags ON psTextPages(tags_extracted);

In this example, tags_extracted is a generated column that extracts the first tag from the tags JSON array. Then, an index is created on this generated column.

Please note that this approach has its limitations. It only indexes the first tag in the JSON array. If you need to index all tags, you might need to reconsider your database design. For example, you could create a separate table for tags and use a many-to-many relationship between pages and tags. This would allow you to index and search tags more efficiently.

trifek's avatar

this query is work fine:: SELECT * FROM psTextPages WHERE JSON_CONTAINS(tags, '"tag 1"'); - it's return me results.

Is posible use like as query in json? in this query I haven't result when I have query: SELECT * FROM psTextPages WHERE JSON_CONTAINS(tags, '"tag"'); (in DB I have tag 1)

Please or to participate in this conversation.