如何從SQLServer中的嵌套JSON中提取資料

DECLARE @CONTACTS varchar(max)
SET @CONTACTS = 
N'[
{"contacts":"[{\"idRole\":1,\"cdsid\":\"RWILS351\"},{\"idRole\":3,\"cdsid\":\"EKANOUS\"},{\"idRole\":126,\"cdsid\":\"RLAESCH1\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"RWILS351\"},{\"idRole\":3,\"cdsid\":\"EKANOUS\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"OINES\"},{\"idRole\":1,\"cdsid\":\"YSEGARRA\"},{\"idRole\":3,\"cdsid\":\"OINES\"},{\"idRole\":3,\"cdsid\":\"TISMAIL3\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DBELL30\"},{\"idRole\":3,\"cdsid\":\"DBELL30\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DBELL30\"},{\"idRole\":3,\"cdsid\":\"DBELL30\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2},{\"pmtGroup\":5}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2},{\"pmtGroup\":5}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":3,\"cdsid\":\"THERMANN\"}]"}]'

SELECT d.idRole, d.cdsid,e.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max) AS JSON
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT '$.idRole',
    cdsid NVARCHAR(50) '$.cdsid',
    idAttribute NVARCHAR(MAX) '$.idAttribute' AS JSON
) as d
CROSS APPLY OPENJSON(d.idAttribute)
WITH (
    pmtGroup NVARCHAR(8) '$.pmtGroup'
) as e;

我需要從 JSON 中獲取值 idRole、cdsid、pmtGroup。我的查詢中缺少什么？我做了一些研究，我嘗試了交叉應用，但沒有回傳資料

uj5u.com熱心網友回復：

問題是其中包含的值contacts不是 JSON 物件，而是包含序列化JSON 物件的字串。

所以你需要洗掉AS JSON它來檢索它。

此外，如果您想獲得pmtGroup不需要的物件OUTER APPLY

SELECT d.idRole, d.cdsid,e.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max)
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT,
    cdsid NVARCHAR(50),
    idAttribute NVARCHAR(MAX) AS JSON
) as d
OUTER APPLY OPENJSON(d.idAttribute)
WITH (
    pmtGroup NVARCHAR(8)
) as e;

或者，您可以只使用路徑$.idAttribute[0].pmtGroup

SELECT d.idRole, d.cdsid,d.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max)
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT,
    cdsid NVARCHAR(50),
    pmtGroup NVARCHAR(8) '$.idAttribute[0].pmtGroup'
) as d;

標籤：sql sql服务器 sql-server-2008

上一篇：如何在SQLAlchemy中使用變數

下一篇：在存盤程序中決議多個同步的逗號分隔輸入