0

I was wondering how to go about breaking down a JSON array that's stored in another JSON, and how to best go about storing it. The JSON is

{
  "InspectionID":1,
  "RoomName":"Test1",
  "Sections":[
    {
      "Name":"Floor",
      "Notes":"Trash"
    },
    {
      "Name":"Floor2",
      "Notes":"Trash2"
    }
  ]
}

Simplest thing in my mind is to just store as a varchar type in my table but doesn't seem to work because it's an array. Also read that its best to break any arrays into different tables with, which i'm fine with also because I can just reference it with RoomID in a new table. But how about do I go about actually breaking down this array and then inserting all the rows in one go? I'm using this currently to break down my JSON array in my SQL Server SP.

CREATE PROCEDURE [API].[AddRoom] (@UserID int, @JsonIn NVARCHAR(MAX), @JsonOut NVARCHAR (MAX) OUTPUT)
AS
   BEGIN
    Declare @InspectionID Int = JSON_VALUE(@JsonIn, '$.InspectionID') 
   Declare @RoomName varchar(200) = JSON_VALUE(@JsonIn, '$.RoomName') 
    Declare @Sections varchar(max) = JSON_VALUE(@JsonIn, '$.Sections') 


   INSERT INTO Rooms (InspectionID, RoomName, Sections)
   SELECT @InspectionID, @RoomName, @Sections

    set @JsonOut = (SELECT 1 [Status], 'Added new room' [Message] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    END

Any info or tips are appreciated!

Improve this question
4
  • Would a for loop work and then a JSON_QUERY()? And then INSERT into table each time it iterates through the size of the array or something? Commented Oct 11, 2021 at 14:55
  • If you want to add more to your question, use the edit feature. Though a Loop is never the right choice; you're writing SQL no C#, you should be using set based methods not iterative ones. Commented Oct 11, 2021 at 14:55
  • What are you expected results here? Commented Oct 11, 2021 at 14:56
  • @Larnu updated my question with the full stored procedure. Just trying to store the JSON that's coming in, into an existing table called Rooms. Don't need to query anything in the SQL with it so would love to just store the whole "Sections" array in single cell of a table, and then return it to the server once its called upon in a separate stored procedure... If that makes sense Commented Oct 11, 2021 at 15:05

2 Answers 2

Reset to default
1

To drill into your JSON array and return all of the data in a single resultset you can use OPENJSON with CROSS APPLY. For your example JSON it would look like:

DECLARE @json NVARCHAR(MAX) = N'{
  "InspectionID":1,
  "RoomName":"Test1",
  "Sections":[
    {
      "Name":"Floor",
      "Notes":"Trash"
    },
    {
      "Name":"Floor2",
      "Notes":"Trash2"
    }
  ]
}';

SELECT l1.InspectionID, l1.RoomName, l2.Name, l2.Notes
FROM OPENJSON(@json)
WITH (
    InspectionID INT,
    RoomName VARCHAR(200),
    Sections NVARCHAR(MAX) AS JSON
) l1
CROSS APPLY OPENJSON(l1.Sections)
WITH (
    [Name] VARCHAR(200),
    [Notes] VARCHAR(1000)
) l2;

This will give you:

InspectionID | RoomName | Name   | Notes
-------------+----------+--------+------
1            | Test1    | Floor  | Trash
1            | Test1    | Floor2 | Trash2

which you can then break into whatever you like with other queries. You just repeat the CROSS APPLY pattern to drill further into deeper object graphs.

If you want to return the array items as JSON in order to store in a table you can just use the first part:

SELECT InspectionID, RoomName, Sections
FROM OPENJSON(@json)
WITH (
    InspectionID INT,
    RoomName VARCHAR(200),
    Sections NVARCHAR(MAX) AS JSON
);

InspectionID | RoomName | Sections
-------------+----------+---------
1            | Test1    | [{...}]

If you want to break it down one level further so that you get a separate row for each item in Sections you can use a CROSS APPLY but just select the value returned for `Sections:

SELECT l1.InspectionID, l1.RoomName, l2.value AS Section
FROM OPENJSON(@json)
WITH (
    InspectionID INT,
    RoomName VARCHAR(200),
    Sections NVARCHAR(MAX) AS JSON
) l1
CROSS APPLY OPENJSON(l1.Sections) l2;

InspectionID | RoomName | Section
-------------+----------+--------
1            | Test1    | {...}  
1            | Test1    | {...}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, should be able to use CROSS APPLY in my SP to store these values :)
I've experimented and got it to store the array as a varchar, but stores it as a string, but realised I still need to return it as an array to my app. So I do need to insert it into separate tables (e.g. RoomSections table) and from there in my SP can select the data and use JSON PATH to convert it back to json with array. My question is with your above method how do I INSERT into this RoomSections table each section in the json? (So the above json will insert 2 rows into RoomSections), or am I going about this the wrong way? Thanks
@JosephLewis I updated my answer, see my edit
thanks for all your help, just still not sure how I can use this to do multiple inserts into a table? Are you able to do an example of using this with actual inserts?
0

I ended up breaking down the array on the server side in C#, and iterating through it and calling another SP each iteration to add the Room details in another table. Doesn't seem to effect performance, so in this case this was the way to go, hope it helps someone else. And if there is a more efficient way in SQL I would also love to hear it, thanks.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.