How can I make my json sql query dynamic

Question

Hello team of experts,

I have a field named MEASUREMENT_DATA_CONFIG in one of my tables named PROCESS_T that contains json code in my ms sql database catalog. Unfortunately I am not able to read the data dynamically in a clean way. If I specify the location in the array e.g. [0] then I can read the value, but the number of values 'Sections' and the number of values 'Measurements' is more than 1 (i.e. 1 to n). Is there a possibility to read out these values dynamically, i.e. instead of just returning the array position [0], select all available positions. If so, what could such a query look like?

Answer 1

To display the arrays vertically, try to adjust this query:

select PROCESS_ID, Name, Version, t2.S_Token, t3.*
from process_t
cross apply openjson(MEASUREMENT_DATA_CONFIG) with ( Sections nvarchar(max) '$.Sections' as json) t1
cross apply openjson(t1.Sections) with ( 
	S_Token nvarchar(max) '$.Token',
	Measurments nvarchar(max) '$.Measurements' as json) t2
cross apply openjson(t2.Measurments) with ( 
	M_Token nvarchar(max) '$.Token',
	M_Name nvarchar(max) '$.Name',
	M_IsReportable nvarchar(max) '$.IsReportable') t3