Creating index on specific JSON value inside an object array

user_1113636 0
Viewed 652

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!

tsql sql-server
3 Answers

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

user_50708790

You could use a computed column with PATINDEX and index that:

CREATE TABLE foo (a varchar(4000), a_ax AS (IIF(PATINDEX('%bar%', a) > 0, SUBSTRING(a, PATINDEX('%bar%', a), 42), '')))

CREATE INDEX foo_x ON foo(a_ax)
user_14430980

You could use a scalar function as @Lukasz Szozda posted - it's a good solution for this. The problem, however, with T-SQL scalar UDFs in computed columns is that they destroy the performance of any query that table is involved in. Not only does data modification (inserts, updates, deletes) slow down, any execution plans for queries that involve that table cannot leverage a parallel execution plan. This is the case even when the computed column is not referenced in the query. Even index builds lose the ability to leverage a parallel execution plan. Note this article: Another reason why scalar functions in computed columns is a bad idea by Erik Darling.

This is not as pretty but, if performance is important than this will get you the results you need without the drawbacks of a scalar UDF. 

CREATE TABLE dbo.jsonStrings
(
  jsonString VARCHAR(8000) NOT NULL, 
  nameTxt AS (
    SUBSTRING(
      SUBSTRING(jsonString,
        CHARINDEX('"Value":"',jsonString,
          CHARINDEX('"Type":"Name",',jsonString,
            CHARINDEX('"DataArray":[',jsonString)+12))+9,8000),1,
      CHARINDEX('"', 
        SUBSTRING(jsonString,
          CHARINDEX('"Value":"',jsonString,
            CHARINDEX('"Type":"Name",',jsonString,
              CHARINDEX('"DataArray":[',jsonString)+12))+9,8000))-1)) PERSISTED
);

INSERT dbo.jsonStrings(jsonString)
VALUES
('{
   "Response":{
      "DataArray":[
         {
            "Type":"Address",
            "Value":"123 Fake St"
         },
         {
            "Type":"Name",
            "Value":"John Doe"
         }
      ]
   }
}');

Note that, this works well for the structure you posted. It may need to be tweaked depending on what the JSON does and can look like.

A second (and better but more complex) solution would be to take the json path logic from Lukasz Szozda's scalar UDF and get it into a CLR. T-SQL scalar UDFs, when written correctly, do not have the aforementioned problems that T-SQL scalar UDFs do.

user_26473420
Related