Question 303632 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to query JSON data with multiple nested arrays in SQL Server?

My business requirement is to store lists of issues & responses as JSON data within a SQL Server 2019 table. Each list corresponds to a page within a document. At minimum, I’d like to be able to retrieve the GUID for a page and its entire list (one row per issue/response). So far, I’ve only been able to return a single page’s list OR its GUID. I’ve tried manipulating the JSON path (both in the OPENJSON function and in its WITH schema description), but I’ve only gotten NULL values for anything past the first level of objects in the Pages array.

JSON:

  {
    "Pages": [
      {
        "DocumentTemplatePageId": "F472F93F-F36B-1410-8C7C-006D55A93A8F",
        "DocumentTemplatePageTypeId": 9,
        "PageData": {
          "IssuesList": [
            {
              "IssueOrderNumber": 1,
              "Description": "Houston, we have a problem.",
              "Resolution": "We are working on it."
            },
            {
              "IssueOrderNumber": 2,
              "Description": "We have issues with employee retention.",
              "Resolution": "We will start giving retention bonuses."
            }
          ]
        }
      },
      {
        "DocumentTemplatePageId": "EB72F93F-F36B-1410-8C7C-006D55A93A8F",
        "DocumentTemplatePageTypeId": 9,
        "PageData": {
          "IssuesList": [
            {
              "IssueOrderNumber": 1,
              "Description": "We have issues with including all the legalese in our advertising.",
              "Resolution": "We will contract to have an editor review all advertising."
            },
            {
              "IssueOrderNumber": 2,
              "Description": "Sales opportunities are lost due to missed callbacks.",
              "Resolution": "We will schedule a dedicated hour within the day for callbacks."
            }
          ]
        }
      },
      {
        "DocumentTemplatePageId": "BA82B33F-F36B-1410-8C7C-006D55A93A8F",
        "DocumentTemplatePageTypeId": 9,
        "PageData": {
          "IssuesList": [
            {
              "IssueOrderNumber": 1,
              "Description": "We have issues with hazardous materials storage.",
              "Resolution": "We will schedule more frequent pickups for hazardous materials."
            },
            {
              "IssueOrderNumber": 2,
              "Description": "We have issues with exhaust fumes in the service area.",
              "Resolution": "We will upgrade our venting equipment for the service area."
            }
          ]
        }
      }
    ]
  }

SQL (returns only a single page’s list, and no page GUID):

DECLARE @templateid NVARCHAR(40) = 'B282B33F-F36B-1410-8C7C-006D55A93A8F'
DECLARE @companyid NVARCHAR(40) = '5A3CDE59-6D80-4E0E-A413-0E91DD24A352'

DECLARE @morejson NVARCHAR(MAX) = (
    SELECT JsonData
    FROM dbo.[CompanyxDocumentTemplate] WITH (NOLOCK)
    WHERE DocumentTemplateId = @templateid
    AND CompanyId = @companyid
)

SELECT *
FROM OPENJSON( @morejson, '$.Pages[0].PageData.IssuesList' ) 
WITH (
    [IssueOrderNumber] INT '$.IssueOrderNumber', 
    [Description] NVARCHAR(250) '$.Description',
    [Resolution] NVARCHAR(250) '$.Resolution'
);

Results:

SSMS results: returning a single list of two elements, extracted from JSON

As you can see, I’m having to include the index of the Pages array; not cool, but I could live with having to loop over the array if necessary. I’ve read through the online Microsoft documentation on JSON data, but it hasn’t helped me in dealing with the nested arrays in my JSON.

Thanks in advance for your help!

Tags:

2

Answers


  1. 0

    I think this is what you’re after.

    If you’ve got nested arrays, you need to call openjson repeatedly on that array until you get down to the level of data you need. Here, I’ve shredded the top level page key and grabbed the $.PageData.IssueList key. One more openjson call on the value from that call, and you get down to the level you’re interested in.

    select 
    DocumentTemplatePageId,
    DocumentTemplatePageTypeId,
    IssueOrderNumber,
    Description,
    Resolution
from openjson(@JSON, '$.Pages')
with
(
    DocumentTemplatePageId nvarchar(4000) '$.DocumentTemplatePageId',
    DocumentTemplatePageTypeId int '$.DocumentTemplatePageTypeId',
    IssuesList nvarchar(max) '$.PageData.IssuesList' as json
) p -- page
cross apply openjson(p.IssuesList)
with
(
    [IssueOrderNumber] INT '$.IssueOrderNumber', 
    [Description] NVARCHAR(250) '$.Description',
    [Resolution] NVARCHAR(250) '$.Resolution'
) i -- issue item

    db<>fiddle

    Login or Signup to reply.
  2. 0

    For every nested array, you need to correlate using CROSS APPLY OPENJSON again to shred that new level. Each OPENJSON can only refer to either a single level, or even with a custom path then only to a single array index.

    Any whole JSON objects or arrays must be pulled out using nvarchar(max) AS JSON otherwise it will null out.

    Note how even the original table can be correlated, an intermediate variable is not needed.

    DECLARE @templateid uniqueidentifier = 'B282B33F-F36B-1410-8C7C-006D55A93A8F';
DECLARE @companyid uniqueidentifier = '5A3CDE59-6D80-4E0E-A413-0E91DD24A352';

SELECT
  j1.DocumentTemplatePageId,
  j1.DocumentTemplatePageTypeId,
  j2.*
FROM dbo.CompanyxDocumentTemplate cdt
CROSS APPLY OPENJSON(cdt.JsonData, '$.Pages')
  WITH (
    DocumentTemplatePageId uniqueidentifier,
    DocumentTemplatePageTypeId int,
    IssuesList nvarchar(max) '$.PageData.IssuesList' AS JSON
  ) j1
CROSS APPLY OPENJSON(j1.IssuesList )
  WITH (
    IssueOrderNumber int,
    Description nvarchar(250),
    Resolution nvarchar(250)
  ) j2
WHERE cdt.DocumentTemplateId = @templateid
  AND cdt.CompanyId = @companyid;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search