请教SQL查询json字符串

FID JSON
1 [{"city": "北京", "name": "张三", "tel": "123"}]


怎么查询


如何写一个数据表函数三个参数 func(key,value,oper)


我调用这个函数传入 func('name','张三','like') 就能得到该条记录。。

SQL JSON
[解决办法]

if OBJECT_ID('tablename', 'u') is not null   drop table #temp;
CREATE TABLE tablename(fid int, json nvarchar(MAX))
insert tablename
select '1','[{"city": "北京", "name": "张三", "tel": "123"}]'
go

CREATE FUNCTION func(@field nvarchar(255), @value nvarchar(MAX), @oper varchar(10))
RETURNS @temp table(fid INT, json NVARCHAR(max))
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
IF(@oper = 'like')
BEGIN
INSERT @temp
select fid, json from tablename where json LIKE '%'+ '"' + @field + '": "' + @value + '"' +'%'
end 
RETURN
end
GO

SELECT * FROM dbo.func('name','张三','like')

/*
fidjson
1[{"city": "北京", "name": "张三", "tel": "123"}]
*/

ALTER FUNCTION func(@field nvarchar(255), @value nvarchar(MAX), @oper varchar(10))
RETURNS @temp table(fid INT, json NVARCHAR(max))
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
IF(@oper = 'like')
BEGIN
INSERT @temp
select fid, json from tablename 
where CHARINDEX('"' + @field + '": "' + @value + '"', json) > 0
end 
RETURN
end
GO