Reading JSON structure from the database (SQL Server)

Question

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

Yitzhak Khabinsky · Accepted Answer · 2022-01-20 17:49:43Z

1

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  |
+----+--------------------------------------+------+------+

edited Jan 20, 2022 at 17:49

answered Jan 20, 2022 at 17:18

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

test9999 Over a year ago

But the problem is, I don't know if this will be in the "External" or "Internal" key. How can I check both keys?

Yitzhak Khabinsky Over a year ago

I updated the answer. Check it out.

napster993 Over a year ago

Please check my code

Yitzhak Khabinsky Over a year ago

I updated my answer with SQL #2.

Yitzhak Khabinsky Over a year ago

Glad to hear that the proposed solution is working for you. Please don't forget to mark it as Answer

RBarryYoung · Accepted Answer · 2022-01-20 17:32:32Z

I do not have access right now ot a server that I could test it on, but if you know that it is in the 'external' branch then I think that this should work:

SELECT       x.Link
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
CROSS APPLY  OPENJSON(m.Formulardata)
  WITH (
    Internal NVARCHAR(MAX) '$.Internal' AS JSON,
    Externals NVARCHAR(MAX) '$.External' AS JSON
  ) AS a
OUTER APPLY OPENJSON(a.Externals)
  WITH (
    Guid NVARCHAR(MAX) '$.GUID',
    Type INT '$.Type',
    Link INT '$.Link'
  ) AS x
WHERE        m.Vorgangsmassnahme_ID = 21 AND v.id = @Vorgang_ID
  AND        ISJSON(m.Formulardata) > 0
  AND        x.Type = 2
  AND        x.GUID = '49383921-5bd8-4cf9-a104-67f957b7fdc7'

Collectives™ on Stack Overflow

Reading JSON structure from the database (SQL Server)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related