sql - 需要从sql server中的长字符串中解析出文本

Question

我需要从下面的 ntext 中检索到的文本是“elapsedTime”：83775

{"timeline":{"events":[{"date":1593613822828,"types":["START"]},{"date":1593613906603,"types":["STOP"]}]},"持续SLAData":null,"completeSLAData":[{"succeeded":true,"goalTime":57600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28800000,"breached": false},"elapsedTime":83775,"remainingTime":57516225,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28716225,"breached":false},"calendarName":"示例 9-5 日历","startTime":1593613822828,"stopTime":1593613906603}],"metricId":48,"definitionChangeDate":0,"definitionChangeMsEpoch":0,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":1593021654383,"updatedDate":1593657022772}

标签： sql

Answer 1

不太好。

假设总是恰好出现一次elapsedTime：

DECLARE @s varchar(1000) = '{"timeline":{"events":[{"date":1593613822828,"types":["START"]},{"date":1593613906603,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":true,"goalTime":57600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28800000,"breached":false},"elapsedTime":83775,"remainingTime":57516225,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28716225,"breached":false},"calendarName":"Sample 9-5 Calendar","startTime":1593613822828,"stopTime":1593613906603}],"metricId":48,"definitionChangeDate":0,"definitionChangeMsEpoch":0,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":1593021654383,"updatedDate":1593657022772}'

DECLARE @i int
SELECT @i = CHARINDEX('elapsedTime":', @s)

DECLARE @j int
SELECT @j = CHARINDEX(',', @s, @i + 13)

SELECT SUBSTRING(@s, @i + 13, @j - @i - 13)

或者，因为它是 JSON：

SELECT JSON_VALUE(@s, '$.completeSLAData[0].elapsedTime')