How to retrieve matched data from mysql table text datatype?

Question

I have one query to build to get matched data from table having value as text in json format.

CREATE TABLE `post_sender_detail` (
  `version_id` int(10) unsigned NOT NULL,
  `subject` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tags` text DEFAULT NULL COMMENT 'JSON of tags details',
  PRIMARY KEY (`version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Values stored like this

version_id	subject	tags
83	test sftp new series	{"1":"Two2","2":"Three 3","7":"one1"}
82	This is testing of seedlist	{"31":"one1","32":"Two2"}
81	non-amp test 001	NULL
80	non-amp test 001	NULL

I want some query like

select * from post_sender_detail where tags match the key as 31;

And get the result like

version_id	subject	tags
82	This is testing of seedlist	{"31":"one1","32":"Two2"}

My DB version

Variable_name	Value
innodb_version	10.4.7
protocol_version	107
version	10.4.7-MariaDB-1:10.4.7+maria~bionic-log7

I tried my way but not getting something to get the desired result. Can you guys help here and let me know if there is any way to get it from MYSQL query?

Thanks

Answer 1

As mentioned in comments by @akina you can use JSON_KEYS to returns the keys from a JSON as array, and MEMBER OF to check if a value is an element of an array :

SELECT *
FROM post_sender_detail
WHERE '31' MEMBER OF( JSON_KEYS(CAST(tags AS JSON)))

Since you are using mariadb 10.4.7 then you can simply do :

select *
from post_sender_detail
where tags like '%"31":%'

Demo here