-2

Database will be having identity linked to each level of SQL. Also would like to merge data of all three table into one master table Table_master which will have final number of data based on inner element record id 2.

expected to update data into table of SQL Server 2016 Table structure with use of Foreign key Also how to export same data into Json back again in a shared network drive folder.

Expected data to be updated in Table

Repro Code:

IF OBJECT_ID('dbo.Table1') IS NULL
    CREATE TABLE dbo.Table1
    (
    Name VARCHAR(100), Location VARCHAR(100), Region VARCHAR(100) 
    )


DECLARE @MYJSON VARCHAR(1000)

SET @MYJSON = '
{
  "Name":"ABC",
  "Location":"East US",
  "Region":"West US",
  "Element":
 [
     {  
        "Name":"IE1",
        "Description":"IE1 Description",
        "Type":"Small",
        "InnerElement":
        [ 
           { "Key":"Name", "Value":"IME1"},
           {"Key":"AnotherProperty","Value":"Value1"}
        ]
     },
     {  
        "Name":"IE2",
        "Description":"IE2 Description",
        "Type":"Medium",
        "InnerElement":
        [ 
           {"Key":"Name","Value":"IME2"},
           {"Key":"Address","Value":"Xyz"}, 
           {"Key":"Type","Value":"Simple"},
           {"Key":"LastProperty","Value":"ValueX"}
        ]
     }
 ]}
 '

SELECT 
JSON_VALUE(@MYJSON,'$.Name') As Name,
JSON_VALUE(@MYJSON,'$.Location') As Location,
JSON_VALUE(@MYJSON,'$.Region') As Region
TheMaster
  • 45,448
  • 6
  • 62
  • 85
UV283
  • 1
  • 1
  • 1
    Please 1. Represent the data as text, not pictures; 2. Tag the actual database you are using, and what integration tool you want to use. 3. Show what you've tried – Nick.Mc May 13 '20 at 12:58
  • Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 –  May 13 '20 at 13:33
  • @Nick.McDermaid I am unable to solve this I have refer following https://stackoverflow.com/questions/49729243/insert-nested-json-array-into-multiple-tables-in-sql-server?answertab=oldest#tab-top solution but unable to achieve what I am expecting to. If possible can you help me through. – UV283 May 13 '20 at 14:22
  • I have edited your question and added some code. Can you add the other table creation definitions and any code that you have actually tried so far. This allows us to quickly copy/paste into our dev environments to assist. You are less likely to help if you don't provide an easy starting point (a repro). Also, not having a repro implies that you haven't tried anything. – Nick.Mc May 14 '20 at 02:43
  • Also, note in your referenced question, the very first comment is _much more convenient to answer if you include CREATE TABLE statements for all the tables_ – Nick.Mc May 14 '20 at 05:11
  • Hi Nick! My approach to this is to use a BULK Column function to import data from a folder for JSON. Currently I am unable to share my SQL Query file as I am still trying. Now I am facing issue of not getting data file open and import due OS access problem. – UV283 May 14 '20 at 07:00
  • I suggest you ask that as a different question. You can stop that being a blocker and continue with this question if you take the time to edit your question and create a repro. The repro I started for you stores JSON in a variable so avoids that issue – Nick.Mc May 14 '20 at 07:51
  • I will be able to share my part of code after 24 hrs of this – UV283 May 14 '20 at 09:30

1 Answers1

0
USE JSON_CAT GO DECLARE @JsonObject NVARCHAR(MAX) 
SELECT @JsonObject=BulkColumn from  openrowset  (BULK 'C:\Users\uverma\Desktop\Test.Json, SINGLE_CLOB)T 
BEGIN TRANSACTION;
INSERT Table1([Name], [Location], [Region])
SELECT [Name], [Location], [Region]
FROM OPENJSON(@JsonObject, '$.InnerElement')
WITH (    [Name] VARCHAR(100),    [Location] VARCHAR(100),    [Region] VARCHAR(100));
DECLARE @Table1Id INT = SCOPE_IDENTITY();
DECLARE @Table3Input TABLE([Table2Id] INT, [InnerMostElement] NVARCHAR(MAX));
MERGE Table2
USING (    SELECT [Name], [Description], [Type], [InnerMostElement]    
FROM OPENJSON(@JsonObject, '$.InnerElement')    
WITH (        [Name] VARCHAR(100),        [Description] VARCHAR(100),        [Type] VARCHAR(100),        [InnerMostElement] NVARCHAR(MAX) AS JSON    )) AS J 
ON 1 = 0    
-- Always INSERT
WHEN NOT MATCHED THEN     
INSERT([Table1Id], [Name], [Description], [Type])    
VALUES (@Table1Id, J.[Name], J.[Description], J.[Type])    
OUTPUT inserted.Id, J.[InnerMostElement]    
INTO @Table3Input([Table2Id], [InnerMostElement]);  

INSERT Table3([Table2Id], [Key], [Value]) 
SELECT [Table2Id], KV.[Key], KV.[Value] 
FROM @Table3Input 
CROSS APPLY (    SELECT [Key], [Value]    
FROM OPENJSON([InnerMostElement])    
WITH (        [Key] VARCHAR(100),[Value] VARCHAR(100))) AS KV;

COMMIT;

Please see I have tried with this and my data is importing as well but value inserting in database are becoming null

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
UV283
  • 1
  • 1
  • @Nick.McDermaid please see i have used above query to get data. data getting inserted as null. – UV283 May 14 '20 at 12:35
  • But I am not getting expected result. – UV283 May 14 '20 at 13:38
  • Thank you Nick with my above SQL code i got my expected result. – UV283 May 14 '20 at 14:21
  • @Nick.McDermaid Can you help how I can read multiple Json files into SQL. – UV283 Jun 09 '20 at 11:02
  • Where is the list of JSON files? You just reuse the above and loop around them. – Nick.Mc Jun 09 '20 at 11:32
  • Multiple Json files are stored on my local drive and needs to be read using my above solution and update in my db. I want to understand how exactly I can loop to read the data. Can you refer me some video or document how to include in my above sql query? List of Json can be like Test001.json to n number of json files. – UV283 Jun 10 '20 at 13:56
  • How can i read a json file using SQL where JSON file naming is "abc_cd_000012347934_20200717201054.json" both numerical value will be varriable. – UV283 Jul 17 '20 at 14:40
  • The problem is that SQL has to already know the name of the files so it can explicitly import them by filename. You could try using `xp_dirtree` but this is usually disabled on most systems https://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html – Nick.Mc Jul 18 '20 at 07:01