JSON\OBJECT

它是一个键值对列表并返回一个 JSON 对象。

创建数据类型为 JSON 列的表

CREATE TABLE Customer (
  id int unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  email varchar(100) NOT NULL,
  address JSON NOT NULL
);

要创建具有 JSON 数据类型的列,请将其指定为 JSON。

向表中插入值

INSERT INTO Customer (name, email, address) VALUES ('Cherish', 'cherish@gmail.com', '{"name": "customerLocation1", "address1": "320 Sector-12F", "address2": "Panaji, Goa", "province": "Goa", "country": "India" }');
INSERT INTO Customer (name, email, address) VALUES ('Clark', 'clark@gmail.com', '{"name": "customerLocation2", "address1": "500 Sector-10F", "address2": "Vaishali, Ghaziabad", "province": "Ghaziabad", "country": "India" }');
INSERT INTO Customer (name, email, address) VALUES ('Peter', 'peter@gmail.com', '{"name": "customerLocation1", "address1": "320 Sector-12F", "address2": "California, USA", "province": "California", "country": "USA" }');

注意: 所有值都应该用单引号引起来,键值对应该用双引号引起来。

对表的选择操作

从表中选择所有数据

SELECT * FROM Customer;

从表中选择自定义 JSON 对象

SELECT JSON_OBJECT(
  'id', -- key
  cs.id, -- value
  'name',  
  cs.name, 
  'email', -- key
  cs.email, -- value
  'address', 
  cs.address 
) FROM Customer cs
WHERE id = 3;

选择 JSON_TYPE

SELECT JSON_TYPE(address) FROM Customer;

从 JSON 对象中选择特定的键值

这可以通过两种方式完成:

• 内联路径运算符 ->>

• 列路径运算符 ->

SELECT address->>"$.address1", address->"$.address1" FROM Customer;

注意: 内联路径运算符保留引号,但列路径运算符包括它们。

你做到了!好工作!

现在您对 JSON\OBJECT 有了完整的了解。尝试用它进行实践练习以获得充分的信心。