在对象数组内的特定 JSON 值上创建索引

假设我的表中有一个 varchar 列，其结构如下：

{
   "Response":{
      "DataArray":[
         {
            "Type":"Address",
            "Value":"123 Fake St"
         },
         {
            "Type":"Name",
            "Value":"John Doe"
         }
      ]
   }
}

我想在“DataArray”数组元素的“Value”字段上创建一个持久计算列，其中包含等于“Name”的类型字段。 （我希望我解释得正确。基本上我想在该结构上索引人名）。

问题是，与其他 json 对象不同，我不能使用JSON_VALUE以一种简单的方式提取所述值的函数。我不知道这是否可以做到，我一直在涉足JSON_QUERY但到目前为止我不知道该怎么办。

任何想法和帮助表示赞赏。谢谢！

您可以使用函数来实现它：

CREATE FUNCTION dbo.my_func(@s NVARCHAR(MAX))
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @r NVARCHAR(100);

   SELECT @r = Value 
   FROM OPENJSON(@s,'$.Response.DataArray')  
   WITH ([Type] NVARCHAR(100) '$.Type', [Value] NVARCHAR(100) '$.Value')
   WHERE [Type] = 'Name';

   RETURN @r;
END;

定义表：

CREATE TABLE tab(
  val NVARCHAR(MAX) CHECK (ISJSON(val) = 1),
  col1 AS dbo.my_func(val) PERSISTED        -- calculated column
);

样本数据：

INSERT INTO tab(val) VALUES (N'{
   "Response":{
      "DataArray":[
         {
            "Type":"Address",
            "Value":"123 Fake St"
         },
         {
            "Type":"Name",
            "Value":"John Doe"
         }
      ]
   }
}');

CREATE INDEX idx ON tab(col1);   -- creating index on calculated column

SELECT * FROM tab;