Creating index on specific JSON value inside an object array

Question

So let's say I have a varchar column in a table with some structure like:

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

And I want to create a persisted computed column on the "Value" field of the "DataArray" array element that contains a Type field that equals "Name". (I hope I explained that properly. Basically I want to index the people names on that structure).

The problem is that, unlike with other json objects, I can't use the JSON_VALUE function in a straightforward way to extract said value. I've no idea if this can be done, I've been dabbling with JSON_QUERY but so far I've no idea what to do.

Any ideas and help appreciated. Thanks!

Answer 1

You could achieve it using function:

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;

Defining table:

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

Sample data:

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;

db<>fiddle demo