SoFunction
Updated on 2025-04-17

MySQL JSON type data query method

1. json object

1.1. Method

  • Use object operations to query:Field->'$.json property'
  • Use functions to query:json_extract(field, '$.json property')
  • Get the JSON array/object length:JSON_LENGTH()

1.2. Data

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increase ID',
  `goods_sn` varchar(25) NOT NULL DEFAULT '' COMMENT 'Product Code',
  `desc_attr` json NOT NULL COMMENT 'Description attributes',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='TEST';
INSERT INTO `test`.`test`(`id`, `goods_sn`, `desc_attr`) VALUES (1, 'A0001', '{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"red\", \"material\": \"nylon\"}');
INSERT INTO `test`.`test`(`id`, `goods_sn`, `desc_attr`) VALUES (2, 'A0002', '{\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"size\": \"LA\", \"color\": \"yellow\", \"material\": \"Pure Cotton"}');

1.3. Query

-- Check products with no empty fabric
select * from test where desc_attr->'$.material' is not null;
select * from test where JSON_EXTRACT(desc_attr, '$.material') is not null;
-- Inquiry of products with pure cotton fabric
select * from test where desc_attr->'$.material'='Pure Cotton';
select * from test where JSON_EXTRACT(desc_attr, '$.material')='Pure Cotton';
-- The number of query tags is greater than2Products
select * from test where JSON_LENGTH(desc_attr->'$.tag')>2;

2. json array

2.1. Method

  • Object operation method query:Field->'$[*].Properties'
  • Use function query:JSON_CONTAINS(field, JSON_OBJECT('json attribute', 'content'))
  • Get the JSON array/object length:JSON_LENGTH()

2.2. Data

CREATE TABLE `test2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increase ID',
  `goods_sn` varchar(25) NOT NULL DEFAULT '' COMMENT 'Product Code',
  `desc_attrs` json NOT NULL COMMENT 'Description attributes,Multiple',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='TEST2';
INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (1, 'A0001', '[{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"red\", \"material\": \"nylon\"}, {\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"size\": \"LA\", \"color\": \"yellow\", \"material\": \"Pure Cotton"}]');
INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (2, 'A0002', '[{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"red\", \"material\": \"nylon\"}, {\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"link\": \"xxx\", \"size\": \"LA\", \"color\": \"yellow\", \"material\": \"Pure Cotton"}]');
INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (3, 'A0003', '[]');

2.3. Query

-- Query items whose description attributes are not empty
select * from test2 where JSON_LENGTH(desc_attrs) > 0;
-- Query the1Items with color attributes
select * from test2 where desc_attrs->'$[0].color' is not null;
-- Query any item with link attributes
select * from test2 where desc_attrs->'$[*].link' is not null;
-- Query any item's existence link is equal toxxxProducts with attributes
select * from test2 where JSON_CONTAINS(desc_attrs,JSON_OBJECT('link', 'xxx'));

Notice

-- [{"link":"xxx"}]
select desc_attrs->'$[*].link' from test2 where id=2;
-- The query result is`["xxx"]`
-- Return to each itemlink，So it's an array

This is the end of this article about MySQL JSON type data query. For more related contents of MySQL JSON type data query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!