JSON string into SQL transaction table

Question

Hi,

I have a json string i want to push the data from json string into transaction table.

below is the json string

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[{	
	"HTable": {
      "HID": "H1001",
      "HDate": "22/08/2023"
    },
    "CTable": {
      "ID": C1001
      "Amount": 200,
      "ETable": {
        "EID": "E1001",
        "EType": "EType1"
      },
      "DTable": [
        {
          "ID": "D1001",
          "Code": "DType1"
        },
        {
          "ID": "D1002",
          "Code": "DType2"
        }
      ],
      "ATable": [
        {
          "ID": "A1001",
          "Code": ACode1,
          "OTable": {
			"ID": "O1001",
            		"Type": "OType1"
          	},
          "OTable": {
			"ID": "O1002",
            		"Type": "OType2"
          	}
        },
        {
          "ID": "A1002",
          "Code": "ACode2"
        }
      ]
    }
  }  
]'

below is my table structure.

create table ##HTable (HID nvarchar(50), HDate DateTime, CID nvarchar(50), CAmt decimal(18,2), EID nvarchar(50), EType nvarchar(50))

insert into ##HTable values ('H1001','2023-08-22','C1001', 200, 'E1001', 'EType1')

create table ##DTable (CID nvarchar(50), DID nvarchar(50), DType nvarchar(50))

insert into ##DTable values ('C1001', 'D1001', 'DType1');

insert into ##DTable values ('C1001', 'D1002', 'DType2');

create table ##ATable (CID nvarchar(50), AID nvarchar(50), ACode nvarchar(50))

insert into ##ATable values ('C1001','A1001','ACode1');

insert into ##ATable values ('C1001','A1002','ACode2');

create table ##OTable (CID nvarchar(50), AID nvarchar(50), OID nvarchar(50), OType nvarchar(50))

insert into ##OTable values ('C1001','A1001','O1001','OType1');

insert into ##OTable values ('C1001','A1001','O1002','OType2');

select * from ##HTable

select * from ##DTable

select * from ##ATable

select * from ##OTable

drop table ##HTable

drop table ##DTable

drop table ##ATable

drop table ##OTable

i want to map like below screen shot

Thanks in Advance

Kasim

Answer 1

Hi @kasim mohamed ,

To retrieve values from a JSON there are several built-in functions you may use. Check this link for more information.

To get you started, here are some examples, I also added a corrected json (there were some quotes missing).

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[{	
	"HTable": {
      "HID": "H1001",
      "HDate": "22/08/2023"
    },
    "CTable": {
      "ID": "C1001",
      "Amount": 200,
      "ETable": {
        "EID": "E1001",
        "EType": "EType1"
      },
      "DTable": [
        {
          "ID": "D1001",
          "Code": "DType1"
        },
        {
          "ID": "D1002",
          "Code": "DType2"
        }
      ],
      "ATable": [
        {
          "ID": "A1001",
          "Code": "ACode1",
          "OTable": {
			"ID": "O1001",
            		"Type": "OType1"
          	},
          "OTable": {
			"ID": "O1002",
            		"Type": "OType2"
          	}
        },
        {
          "ID": "A1002",
          "Code": "ACode2"
        }
      ]
    }
  }  
]'

When you just want a single value and do not have a JSON array you can use:

INSERT INTO ##HTable (HID ) 
SELECT	JSON_VALUE(value,'$.HTable.HID')
FROM	OPENJSON(@json)

When you are working with arrays, you will come up with something like this:

INSERT INTO ##DTable (CID) 
SELECT  JSON_VALUE(DTable.value,'$.ID')
FROM	OPENJSON(@json)
CROSS APPLY OPENJSON(value,'$') main
CROSS APPLY OPENJSON(main.value,'$.DTable') DTable

Hope this will help you.

Kind regards,

Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".