sql - JSON - 使用运算符选择值？

Question

所以我正在使用如下所示的数据集:查询:

Select Key, NativeObject
FROM dbo.UserResource

示例结果:

Key    | NativeObject
1121   |{   "NativeID": "3452",   "AccountType": "Active",   "AdNumber": "",   "AdditionalDimensions": "",   "UserDescription": "User", "IsDeleted": "0" }

我遇到问题的一些场景是:

1. 我想提取 AdNumber 不为空的键
2. 我想提取值大于 X 的 NativeID(即)NativeID >= 3000

目前，我正在将其添加到 Select 子句中，但我不知道如何添加如上所述的参数。任何帮助将不胜感激!

json_value('{"IsDeleted": "1"}', '$.IsDeleted') as 'Output'

Answer 1

我创建了一个表并使用以下代码将值插入到该表中:

CREATE TABLE UserResources( KeyId INT ,NativeObject 
NVARCHAR(4000)CONSTRAINT PK_Car PRIMARY KEY(keyID),
CONSTRAINT IsValidJSON1 CHECK (ISJSON(NativeObject) = 1));


insert into UserResources(KeyId,NativeObject) VALUES(4,'{"NativeID": "3452",   "AccountType": "Active",   "AdNumber": "",   "AdditionalDimensions": [{"right":1}],   "UserDescription": "User", "IsDeleted": "0" }'),
(2,'{"NativeID": "3452",   "AccountType": "Active",   "AdNumber": "",   "AdditionalDimensions": [{"left":2}],   "UserDescription": "User", "IsDeleted": "1" }'),
(3,'{"NativeID": "3452",   "AccountType": "Active",   "AdNumber": "",   "AdditionalDimensions": [{"left":3}],   "UserDescription": "User", "IsDeleted": "0" }');

我使用以下代码从 JSON 列中选择了值:

SELECT KeyId, NativeObject = JSON_QUERY(NativeObject, '$.AdditionalDimensions')
  FROM dbo.UserResources;

我得到以下结果:

通过这种方式，您可以选择 JSON 的值。