SQLserver2016对字段是json对象查询
现在2016内置对json对象的查询支持了.正好项目中用到,做个备忘
如果字段内容是:
[{"tagid":"100015","orderid":"0","tagname":"法律服务","taglevel":"3"},{"tagid":"110035","orderid":"0","tagname":"政策法规","taglevel":"3"}]
这样的json
sql语句:
SELECT AId,GovLable ,ProductArra.tagname FROM dbo.Tab_GovArticleLableRel CROSS APPLY OPENJSON (Tab_GovArticleLableRel.ProcuctLable)
WITH (tagid INT N'$.tagid',
orderid INT N'$.orderid',
tagname NVARCHAR (200) N'$.tagname',
taglevel INT N'$.taglevel'
) AS ProductArra
WHERE ProductArra.tagid='100015'
如果字段是:
{"prov":[{"pname":"贵州"},{"pname":"江苏"}],"city":[{"cname":"北京市"},{"cname":"荆门市"}]}
sql语句:
SELECT AId,GovLable ,AreaLableArraProv.pname,AreaLableArraCity.cname
FROM dbo.Tab_GovArticleLableRel
CROSS APPLY OPENJSON (Tab_GovArticleLableRel.AreaLable,N'$.prov')
WITH (pname nvarchar(max) N'$.pname') AS AreaLableArraProv
CROSS APPLY OPENJSON (Tab_GovArticleLableRel.AreaLable,N'$.city')
WITH (cname nvarchar(max) N'$.cname') AS AreaLableArraCity
WHERE AreaLableArraProv.pname='贵州'AND AreaLableArraCity.cname='荆门市'
如果字段是:
{"prov":["贵州","江苏","新疆","湖北","山东","山西","北京","安徽","河南","福建","上海"],"city":["北京市","荆门市","青岛市"]}
根据数组内容查询,还不会..