sqlserver、mysql、sqlite json类型数据查询及索引优化

 

sqlserver:

#query
SELECT SalesOrderNumber,
    OrderDate,
    JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell' 

#index
ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

 

mysql:

#query
SELECT val->>'$.child.id' AS id,val->>'$.child.name' AS name FROM tab_name WHERE ID > 0 and val->>'$.child.id'>=2

#index
alter table tab_name add column vname varchar(40) generated always  as (val->"$.child.name") virtual;

CREATE INDEX `name_idx` ON `tab_name`(`vname`);  

 

sqlite:

#query
SELECT ifnull(json_extract(dec,"$.ID"),0) as ID,val FROM tab_name;