Reading JSON structure from the database (SQL Server)

I'm using a SQL Server database. I have a table which has a field that stores the following JSON file structure:

{
   "Internal":[
      {
         "GUID":"7c2fc2b3-7ae2-42c5-b6f0-13137f58348c",
         "Type":1,
         "ID":155
      },
      {
         "GUID":"8774cbcb-e594-4c64-8ecb-b71d4f97cea4",
         "Type":2,
         "Link":134
      }
   ],
   "External":[
      {
         "GUID":"be41536b-33ea-4e12-8a11-544aa15c1edb",
         "Type":2,
         "Link":174
      },
      {
         "GUID":"49383921-5bd8-4cf9-a104-67f957b7fdc7",
         "Type":1,
         "Link":202
      }
   ]
}

My problem is finding the GUID value. How do I find the value of Link (202), if I know that the GUID I'm looking for is 49383921-5bd8-4cf9-a104-67f957b7fdc7?

I know the solution is to use the CROSS APPLY but I can't come to a solution. I looked through a few threads in the forum, but after a few hours I still can't get the correct query, so I am posting my question on the forum.

Here is my initial query which selects a JSON structure from the database:

SELECT       m.Formulardata -- here is a JSON structure
FROM         [dbo].[Vorgaenge] v
LEFT JOIN    [dbo].[Vorgangsschritte] s ON v.id = s.Vorgang_ID
LEFT JOIN    [dbo].[Vorgangsmassnahmen] m ON s.id = m.Vorgangsschritt_ID
WHERE        m.Vorgangsmassnahme_ID = 21 AND v.id = @Vorgang_ID

Please try the following solution.

SQL #1 is a minimal reproducible example.

SQL #2 is close as much as possible to your environment.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Formulardata  NVARCHAR(MAX));
INSERT INTO @tbl (Formulardata) VALUES
(N'{
   "Internal":[
      {
         "GUID":"7c2fc2b3-7ae2-42c5-b6f0-13137f58348c",
         "Type":1,
         "ID":155
      },
      {
         "GUID":"8774cbcb-e594-4c64-8ecb-b71d4f97cea4",
         "Type":2,
         "Link":134
      }
   ],
   "External":[
      {
         "GUID":"be41536b-33ea-4e12-8a11-544aa15c1edb",
         "Type":2,
         "Link":174
      },
      {
         "GUID":"49383921-5bd8-4cf9-a104-67f957b7fdc7",
         "Type":1,
         "Link":202
      }
   ]
}');
-- DDL and sample data population, end

SELECT ID, u.* 
FROM @tbl
CROSS APPLY OPENJSON(Formulardata) x
CROSS APPLY OPENJSON(x.Value)
WITH
(
   GUID  UNIQUEIDENTIFIER   '$.GUID',
   Type  INT                '$.Type',
   Link  INT                '$.Link'
) AS u
-- WHERE u.Link = 202;

SQL #2

;WITH rs AS
(
    SELECT       m.Formulardata -- here is a JSON structure
    FROM         [dbo].[Vorgaenge] v
    LEFT JOIN    [dbo].[Vorgangsschritte] s ON v.id = s.Vorgang_ID
    LEFT JOIN    [dbo].[Vorgangsmassnahmen] m ON s.id = m.Vorgangsschritt_ID
    WHERE        m.Vorgangsmassnahme_ID = 21 AND v.id = @Vorgang_ID
)
SELECT ID, u.* 
FROM rs
CROSS APPLY OPENJSON(Formulardata) x
CROSS APPLY OPENJSON(x.Value)
WITH
(
   GUID  UNIQUEIDENTIFIER   '$.GUID',
   Type  INT                '$.Type',
   Link  INT                '$.Link'
) AS u
WHERE u.Link = 202;

Output

+----+--------------------------------------+------+------+
| ID |                 GUID                 | Type | Link |
+----+--------------------------------------+------+------+
|  1 | 7c2fc2b3-7ae2-42c5-b6f0-13137f58348c |    1 | NULL |
|  1 | 8774cbcb-e594-4c64-8ecb-b71d4f97cea4 |    2 | 134  |
|  1 | be41536b-33ea-4e12-8a11-544aa15c1edb |    2 | 174  |
|  1 | 49383921-5bd8-4cf9-a104-67f957b7fdc7 |    1 | 202  |
+----+--------------------------------------+------+------+

Reading JSON structure from the database (SQL Server)

Related

Recent Posts