how to filter data by Json string column in specific field

Question

I have a Ms Sql Table. In this table, I store data in json format in my column named "DynamicData". What I want to do is to filter on this table. But to do this filtering according to different fields on the json data in my column named "dynamicData".

e.g;

Id | Title | DynamicData
---------------------------------------------------------------- ------------------------------
1 | Title1 | { "Id": 1, "Score": 5, "Message": "Msg1", Types: ["a", "b", "c"]}

2 | Title2 | { "Id": 2, "Score": 3, "Message": "Msg2", Types: ["z", "k", "c"]}

3 | Title3 | { "Id": 3, "Score": 1, "Message": "Msg3", Types: ["a", "b", "c"]}

4 | Title4 | { "Id": 4, "Score": 4, "Message": "Msg4", Types: ["h", "n", "f"]}

Exactly what I want; querying in different ways according to the fields in this json data.

For example;

Retrieve the record whose "score" field is "5". Bring the record with "Score" field "5" and "Message" field "Msg1". Fetch the record with the "Score" field "5" or the "Types" field "h" or "n".

There may be different and somewhat more complex inquiries similar to these. I searched but couldn't find what I wanted. Do you have a suggestion or a sample application on how I can make such inquiries?

Ditto Tim's question ^^^ this will impact the options available to you. — Alan Burstein, Aug 17 '21 at 18:07

score 1 · Accepted Answer · answered Aug 17 '21 at 17:57

If you are SQL Server 2016+ you can use OPENJSON()

Here's an example of how you can filter on the Score:

DECLARE @testdata TABLE
    (
        [Id] INT
      , [Title] NVARCHAR(20)
      , [DynamicData] NVARCHAR(MAX)
    );


INSERT INTO @testdata (
                          [Id]
                        , [Title]
                        , [DynamicData]
                      )
VALUES ( 1, 'Title1', '{ "Id": 1, "Score": 5, "Message": "Msg1", "Types": ["a", "b", "c"]}' )
     , ( 2, 'Title2', '{ "Id": 2, "Score": 3, "Message": "Msg2", "Types": ["z", "k", "c"]}' )
     , ( 3, 'Title3', '{ "Id": 3, "Score": 1, "Message": "Msg3", "Types": ["a", "b", "c"]}' )
     , ( 4, 'Title4', '{ "Id": 4, "Score": 4, "Message": "Msg4", "Types": ["h", "n", "f"]}' );


SELECT *
FROM   @testdata [td]
CROSS APPLY OPENJSON([td].[DynamicData])
           WITH (
                    [Id] INT
                  , [Score] INT
                  , [Message] NVARCHAR(20)
                  , [Types] NVARCHAR(MAX) AS JSON
                ) AS [dd]
WHERE  [dd].[Score] = 5

Giving you the results of:

Id          Title                DynamicData                                                         Id          Score       Message  Types
----------- -------------------- ------------------------------------------------------------------- ----------- ----------- -------  -----
1           Title1               { "Id": 1, "Score": 5, "Message": "Msg1", "Types": ["a", "b", "c"]} 1           5           Msg1     ["a", "b", "c"]

To get to "Types" we'll use anther CROSS APPLY since "Types" is an array:

SELECT *
FROM   @testdata [td]
CROSS APPLY OPENJSON([td].[DynamicData])
           WITH (
                    [Id] INT
                  , [Score] INT
                  , [Message] NVARCHAR(20)
                  , [Types] NVARCHAR(MAX) AS JSON
                ) AS [dd]
CROSS APPLY OPENJSON([Types]) dt --Add another cross apply for types since it is an array
WHERE  [dd].[Score] = 5
OR dt.[Value] IN ('h','n') --Then we can filter on it

Giving results of:

Id          Title                DynamicData                                                            Id          Score       Message   Types            key  value   type
----------- -------------------- ---------------------------------------------------------------------- ----------- ----------- --------- ---------------- ---- ------- ----
1           Title1               { "Id": 1, "Score": 5, "Message": "Msg1", "Types": ["a", "b", "c"]}    1           5           Msg1      ["a", "b", "c"]  0    a       1
1           Title1               { "Id": 1, "Score": 5, "Message": "Msg1", "Types": ["a", "b", "c"]}    1           5           Msg1      ["a", "b", "c"]  1    b       1
1           Title1               { "Id": 1, "Score": 5, "Message": "Msg1", "Types": ["a", "b", "c"]}    1           5           Msg1      ["a", "b", "c"]  2    c       1
4           Title4               { "Id": 4, "Score": 4, "Message": "Msg4", "Types": ["h", "n", "f"]}    4           4           Msg4      ["h", "n", "f"]  0    h       1
4           Title4               { "Id": 4, "Score": 4, "Message": "Msg4", "Types": ["h", "n", "f"]}    4           4           Msg4      ["h", "n", "f"]  1    n       1

You see there are duplicates, with the CROSS APPLY on "Types", each element in that array is now it's own row, shown in the Value column.

You will have to make adjustments for your specific requirement, but this should get you moving in the right direction.

score 0 · Answer 2 · answered Aug 17 '21 at 18:40

If you can't use JSON there is always Ngrams8k.

DECLARE @table TABLE (ID INT IDENTITY, Title VARCHAR(20), DynamicData VARCHAR(1000));

INSERT @table(Title,DynamicData) VALUES
('Title1', '{"Id": 1, "Score": 5, "Message": "Msg1", Types: ["a", "b", "c"]}'),
('Title2', '{"Id": 2, "Score": 3, "Message": "Msg2", Types: ["z", "k", "c"]}'),
('Title3', '{"Id": 3, "Score": 1, "Message": "Msg3", Types: ["a", "b", "c"]}'),
('Title4', '{"Id": 4, "Score": 4, "Message": "Msg4", Types: ["h", "n", "f"]}');

DECLARE @score INT = 5, @type CHAR(1) = 'b'

SELECT      t.*
FROM        @table AS t
CROSS APPLY PerfLib.samd.ngrams8K(t.DynamicData,7) AS ng
CROSS APPLY (VALUES(SUBSTRING(t.DynamicData,ng.Position+8,8000))) AS pfx(Txt)
CROSS APPLY (VALUES(CHARINDEX('[',t.DynamicData),
                    CHARINDEX(']',t.DynamicData))) AS tp(Lft,Rgt)
CROSS APPLY (VALUES(REPLACE(SUBSTRING(
                    t.DynamicData,tp.Lft+1,tp.Rgt-tp.Lft-1),'"',''))) AS tl(List)
CROSS APPLY (VALUES(SUBSTRING(pfx.Txt,1,CHARINDEX(',',pfx.Txt)-1)))   AS f(Score)
WHERE       ng.Token = '"Score"'
  AND       f.Score = @score
  AND       EXISTS (SELECT 1 FROM STRING_SPLIT(tl.List,',') AS split
                    WHERE LTRIM(split.[value]) = @type);

Returns:

ID  Title  DynamicData                                                      Position
--- ------ ---------------------------------------------------------------- --------
1   Title1 {"Id": 1, "Score": 5, "Message": "Msg1", Types: ["a", "b", "c"]} 11

how to filter data by Json string column in specific field

2 Answers2