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?
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.