Processing JSON in SQL table

Processing a column in a SQL table [MyTable] that has JSON data.
Row 1:

{
	"changedAttributes": [
		{
			"logicalName": "name",
			"oldValue": null,
			"newValue": "Insufficient Notice Fee"
		},
		{
			"logicalName": "transfer",
			"oldValue": null,
			"newValue": "False"
		},
		{
			"logicalName": "adjusted",
			"oldValue": null,
			"newValue": "False"
		},
		{
			"logicalName": "businessunit",
			"oldValue": null,
			"newValue": "businessunit,ee9deb16-c1b6-4993-a596-0576882e153c"
		}
	]
}

Row 2:

{
	"changedAttributes": [
		{
			"logicalName": "areatemplate",
			"oldValue": null,
			"newValue": "jobwalkareatemplate,458e64e4-88ab-4702-9711-3c76f4df4339"
		},
		{
			"logicalName": "businessunit",
			"oldValue": null,
			"newValue": "businessunit,a397d0ec-2760-45fe-a930-0c99c70c0151"
		},
		{
			"logicalName": "deferred",
			"oldValue": "1",
			"newValue": "0"
		}
	]
}

Desired Output:

Logical Name	oldValue	newValue
name	null	Insufficient Notice Fee
transfer	null	false
adjusted	null	False
businessunit	null	businessunit,ee9deb16-c1b6-4993-a596-0576882e153c
areatemplate	null	jobwalkareatemplate,458e64e4-88ab-4702-9711-3c76f4df4339
owningbusinessunit	null	businessunit,a397d0ec-2760-45fe-a930-0c99c70c0151
deferred	1	0

SQL Query to produce desired output

select 
	  logicalName = JSON_VALUE(b.value, '$.logicalName')
	, oldValue = JSON_VALUE(b.value, '$.oldValue')
	, newValue = JSON_VALUE(b.value, '$.newValue')
from MyTable a
cross apply openjson(json_query(a.JsonData, '$.changedAttributes')) b

Last modified by Mohit @ 4/4/2025 8:14:30 AM